Fols Scripts

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.up_Services_GetBookingInterfacesInfos Script Date: 05/05/2008 09:00:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_Services_GetBookingInterfacesInfos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_Services_GetBookingInterfacesInfos]
GO

/******************************************************************************************
** File :
** Name : up_Services_GetBookingInterfacesInfos
** Desc : Get interfaces informations
**
** Return values:
** - 0 : OK
** - 1 : KO
**
** Called by : BUSINESS\Services.cls : GetBookingInterfacesInfos
**
**
** Parameters:
** Input
** ----------
** @HotelId Type : int
** @LangId Type : int
** @BookId Type : int
**
** Output
** ----------
** none
**
** Auth: MHT
** Date: 18/12/2003
*******************************************************************************************
** Change History
*******************************************************************************************
** Date: Author: Description:
** 05/05/08 CCU 1.3 CR6197/387 - New field INTE_LEVEL_ID for restriction level (PBX)
** 19/05/08 CCU 1.4 CR6197/387 - Code review
** 17/06/08 XV 1.5 CR6394/388 - Manage Pay TV interface
** 16/04/12 MF 1.6/700 CR7346 - Add enum code and enum tars corresp in return / sort by id
** 03/01/13 MF 1.7 CR8081/7.01 - Add management of internet interface
** 16/06/13 RS 1.8 CR10046/7.02 - [Popup Interface] Interface Internet Lot 2 (EVOLUTION ALLEMAGNE FOR 78)
******************************************************************************************/
CREATE Procedure dbo.up_Services_GetBookingInterfacesInfos
/* Param List */
@HotelId int,
@LangId int,
@BookId int

AS

--Disable the counter
set nocount on

--Declare variables
declare @SQLError int
declare @ProcName nvarchar(50)
declare @CLASS_OF_SERVICE int
declare @IntLevel_No_Restriction_PABX int
declare @IntLevel_No_Restriction_PTV int
declare @IntLevel_No_Restriction_INTERNET int
declare @Is_Allow_Posting_PABX bit = 0
declare @Is_Allow_Posting_PTV bit = 0
declare @Is_Allow_Posting_INTERNET bit = 0
declare @Has_Allow_Posting_PABX bit = 0
declare @Has_Allow_Posting_PTV bit = 0
declare @Has_Allow_Posting_INTERNET bit = 0
declare @Param_Allow_Posting_PABX int = 0
declare @Param_Allow_Posting_PTV int = 0
declare @Param_Allow_Posting_INTERNET int = 0
declare @cst_Param_Activated int
declare @cst_Interface_Opened int
declare @RC int
declare @cst_Param_AllowPostingActivated int = 1

Set @cst_Param_Activated = 1
Set @cst_Interface_Opened = 1

--Get the Procedure Name
Select @ProcName = object_name(@@procid)
set @SQLError = @@error if @SQLError <> 0 goto ErrManagement

Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'PABX_CLASS_OF_SERVICE', @ParamValue = @CLASS_OF_SERVICE Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

If @CLASS_OF_SERVICE = @cst_Param_Activated
BEGIN
Exec @RC = up_Interface_GetDefaultCOS @Hoteid_IN = @HotelId,
@IntEnum_code_IN ='TEL',
@LangId_IN = @LangId ,
@IntLevelNoRestriction_OUT = @IntLevel_No_Restriction_PABX Output
Set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement
END

Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'PTV_CLASS_OF_SERVICE', @ParamValue = @CLASS_OF_SERVICE Output
Set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

If @CLASS_OF_SERVICE = @cst_Param_Activated
Begin
Exec @RC = up_Interface_GetDefaultCOS @Hoteid_IN = @HotelId,
@IntEnum_code_IN = 'PTV',
@LangId_IN = @LangId ,
@IntLevelNoRestriction_OUT = @IntLevel_No_Restriction_PTV Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement
End

Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'INTERNET_CLASS_OF_SERVICE', @ParamValue = @CLASS_OF_SERVICE Output
Set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

If @CLASS_OF_SERVICE = @cst_Param_Activated
Begin
Exec @RC = up_Interface_GetDefaultCOS @Hoteid_IN = @HotelId,
@IntEnum_code_IN = 'WWW',
@LangId_IN = @LangId ,
@IntLevelNoRestriction_OUT = @IntLevel_No_Restriction_INTERNET Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement
End

--Is PABX Allow Posting activated ?
Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'ALLOW_POSTING_PABX' , @ParamValue = @Param_Allow_Posting_PABX Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

--Is PTV Allow Posting activated ?
Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'ALLOW_POSTING_PTV', @ParamValue = @Param_Allow_Posting_PTV Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

--Is INTERNET Allow Posting activated ?
Exec @RC = up_param_getvalue @HotelId = @HotelId, @ParamCode = 'ALLOW_POSTING_INTERNET' , @ParamValue = @Param_Allow_Posting_INTERNET Output
set @SQLError = @@error if @SQLError <> 0 Or @RC <> 0 goto ErrManagement

-- Set default value allow posting PABX
If @Param_Allow_Posting_PABX = @cst_Param_AllowPostingActivated BEGIN
Set @Is_Allow_Posting_PABX = 0
Set @Has_Allow_Posting_PABX = 1
END

-- Set default value allow posting PTV
If @Param_Allow_Posting_PTV = @cst_Param_AllowPostingActivated BEGIN
Set @Is_Allow_Posting_PTV = 0
Set @Has_Allow_Posting_PTV= 1
END

-- Set default value allow posting INTERNET
If @Param_Allow_Posting_INTERNET = @cst_Param_AllowPostingActivated BEGIN
Set @Is_Allow_Posting_INTERNET = 1
Set @Has_Allow_Posting_INTERNET = 1
END

--Create temporary table
create table #INTERFACES
(
SERV_ID int,
ENUM_ID int,
ENUM_LABEL nvarchar(200),
ENUM_CODE nvarchar(200),
ENUM_TARS_CORRESP nvarchar(200),
IS_OPEN bit,
HAS_ALLOW_POSTING bit,
IS_ALLOW_POSTING bit,
INTE_LEVEL_ID int
)
set @SQLError = @@error if @SQLError <> 0 goto ErrManagement

--Select existing interfaces
insert into #INTERFACES
(
SERV_ID,
ENUM_ID,
ENUM_LABEL,
ENUM_CODE,
ENUM_TARS_CORRESP,
IS_OPEN,
HAS_ALLOW_POSTING,
IS_ALLOW_POSTING,
INTE_LEVEL_ID
)
select I.SERV_ID,
I.INTY_ID,
E.ENUM_LABEL,
E.ENUM_CODE,
E.ENUM_TARS_CORRESP,
I.INTE_OPEN,
Case When E.ENUM_CODE = 'TEL' Then -- HAS ALLOW POSTING FLAG
@has_Allow_Posting_PABX
Else
Case When E.ENUM_CODE = 'PTV' Then
@has_Allow_Posting_PTV
Else
Case When E.ENUM_CODE = 'WWW' Then
@has_Allow_Posting_INTERNET

ELSE 0 END
END
END,
I.INTE_ALLOW_POSTING,
I.INTE_LEVEL_ID
from INTERFACE I with (readUncommitted)
inner join SERVICES S with (readcommitted)
on S.HOTE_ID = @HotelId
and S.SERV_ID = I.SERV_ID
and S.BOOK_ID = @BookId
and S.SERV_ID = S.SERV_MASTER_ID
inner join ENUM E with (readUncommitted)
on E.ENUM_ID = I.INTY_ID
and E.ENUM_TYPE = 'InterTyp'
and E.LANG_ID = @LangId
where I.HOTE_ID = @HotelId
set @SQLError = @@error if @SQLError <> 0 goto ErrManagement

--Select non existing interfaces (--Open by default)
--insert into #INTERFACES
--(
-- SERV_ID,
-- ENUM_ID,
-- ENUM_LABEL,
-- ENUM_CODE,
-- ENUM_TARS_CORRESP,
-- IS_OPEN,
-- HAS_ALLOW_POSTING,
-- IS_ALLOW_POSTING,
-- INTE_LEVEL_ID
--)
--select S.SERV_ID,
-- E.ENUM_ID,
-- E.ENUM_LABEL,
-- E.ENUM_CODE,
-- E.ENUM_TARS_CORRESP,
-- @cst_Interface_Opened,
-- Case When E.ENUM_CODE = 'TEL' Then -- HAS ALLOW POSTING FLAG
-- @has_Allow_Posting_PABX
-- Else
-- Case When E.ENUM_CODE = 'PTV' Then
-- @has_Allow_Posting_PTV
-- Else
-- Case When E.ENUM_CODE = 'WWW' Then
-- @has_Allow_Posting_INTERNET

-- ELSE 0 END
-- END
-- END,

-- Case When E.ENUM_CODE = 'TEL' Then -- IS ALLOW POSTING CHECKBOX
-- @Is_Allow_Posting_PABX
-- Else
-- Case When E.ENUM_CODE = 'PTV' Then
-- @Is_Allow_Posting_PTV
-- Else
-- Case When E.ENUM_CODE = 'WWW' Then
-- @Is_Allow_Posting_INTERNET

-- ELSE 0 END
-- END
-- END,

-- CASE WHEN E.ENUM_CODE = 'TEL' THEN @IntLevel_No_Restriction_PABX
-- ELSE
-- CASE WHEN E.ENUM_CODE = 'PTV' THEN @IntLevel_No_Restriction_PTV
-- ELSE
-- CASE WHEN E.ENUM_CODE = 'WWW' THEN @IntLevel_No_Restriction_INTERNET
-- ELSE
-- Null
-- END
-- END
-- END
--from SERVICES S with (readcommitted)
--inner join ENUM E with (readUncommitted)
--on E.ENUM_TYPE = 'InterTyp'
--and E.LANG_ID = @LangId
--inner join HOTEL_PARAM HP with (readUncommitted)
--on HP.HOTE_ID = @HotelId
--and HP.Para_Type = 2
--and HP.PARA_ID = E.ENUM_MASTER_ID
--and HP.PARA_VALUE = 1 --Interface installed in hotel
--where S.HOTE_ID = @HotelId
--and S.BOOK_ID = @BookId
--and S.SERV_ID = S.SERV_MASTER_ID
--and not exists ( select 1 from #INTERFACES I where S.SERV_ID = I.SERV_ID )
--set @SQLError = @@error if @SQLError <> 0 goto ErrManagement

--Final select
select * from #INTERFACES
order by enum_id
set @SQLError = @@error if @SQLError <> 0 goto ErrManagement

--Error Management
ErrManagement:
if @SQLError <> 0
Begin
RAISERROR (' Error in the procedure %s with "%s" - ERROR NUMBER : %d ', 16, -1, @ProcName, ' ', @SQLError)
Return @SQLError
End

-- Return
RETURN 0

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[up_Services_GetBookingInterfacesInfos] TO [FolsUsers]
GO