Fols Scripts

-- QC22757 et QC22706
-- recherche des chambres avec un RCLCA_ID a ZERO
-- Le fait que le RCLCA_ID soit a ZERO est un bug !

SELECT r.room_num, rs.rclca_id, * FROM room r
inner join ROOM_STATUS rs
on r.room_id = rs.ROOM_ID
WHERE rs.RCLCA_ID = 0
-- Le resultat peut afficher 1 ou plusieurs lignes
--------------------------------------------------------
-- Modification du status de la chambre.
update ROOM_STATUS SET rclca_id = 1
WHERE rclca_id = 0


-- Le script ci-dessous permet de corriger ce probleme definitivement
-- ============================================================================
-- Purpose Re-create table ROOM_STATUS
-- Nom de SGBD Microsoft SQL Server 2000
-- Date de creation 24032006
-- Author FB
-- Version 4.0.0 - CR2348
-- ============================================================================
-- 150607 TS 140CR4884 - Rollback if it's nessecary
-- 08112012 TS 7.01CR8769
-- 28-11-2019 AS tg_ROOM_STATUS_RCLCA_ID_UPD created
-- ============================================================================

DECLARE @msg nvarchar(255)
DECLARE @SQLError INT
SET NOCOUNT ON
SET @SQLError=0
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
BEGIN TRAN
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID = OBJECT_ID('ROOM_STATUS') AND TYPE = 'U')
OR (NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'SERV_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'RSTA_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_OOS_RSON_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_OOS_DATE_START')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_OOS_DATE_END')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_OOS_REASON')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'RCLCA_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'RCLCA_EXTST_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_MINMAIN_ID')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_MINMAIN_DATE_START')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_MINMAIN_DATE_END')
AND NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_MINMAIN_REASON'))
BEGIN
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID = OBJECT_ID('ROOM_STATUS') AND TYPE = 'U')
DROP TABLE ROOM_STATUS;
END
IF NOT EXISTS(SELECT 1 FROM sys.tables where name =N'ROOM_STATUS')
BEGIN
--
-- CREATE THE NEW ROOM_STATUS TABLE
--
CREATE TABLE [dbo].[ROOM_STATUS] (
[HOTE_ID] [T_key] NOT NULL ,
[ROST_ID] [T_key] IDENTITY (1, 1) NOT NULL ,
[ROOM_ID] [T_key] NOT NULL ,
[RSTA_ID] [T_key] NOT NULL,
[SERV_ID] [T_key] NULL,
[ROST_OOS_RSON_ID] [T_key] NULL,
[ROST_OOS_DATE_START] [T_date] NULL,
[ROST_OOS_DATE_END] [T_date] NULL,
[ROST_OOS_REASON] [T_nlabel200] NULL,
[RCLCA_ID] [T_key] NOT NULL,
[RCLCA_EXTST_ID] [T_key] NULL,
[ROST_MINMAIN_ID] [T_key] NULL,
[ROST_MINMAIN_DATE_START] [T_date] NULL,
[ROST_MINMAIN_DATE_END] [T_date] NULL,
[ROST_MINMAIN_REASON] [T_nlabel200] NULL,
[ROST_USER_CREA] [t_nlabel20] NULL ,
[ROST_USER_CHANGE] [t_nlabel20] NULL,
[ROST_DATE_CREA] [T_date] NULL,
[ROST_DATE_CHANGE] [T_date] NULL
) ON [DEFAULT]
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

--
-- DEFINE THE DEFAULT CONSTRAINT COLUMN
--
ALTER TABLE [dbo].[ROOM_STATUS] WITH NOCHECK ADD
CONSTRAINT [DF_ROOM_STATUS_DATE_CREA] DEFAULT (getdate()) FOR [ROST_DATE_CREA],
CONSTRAINT [DF_ROOM_STATUS_DATE_CHANGE] DEFAULT (getdate()) FOR [ROST_DATE_CHANGE]
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

--
-- DEFINE PRIMARY KEY PK_ROOM_STATUS
--
ALTER TABLE [dbo].[ROOM_STATUS] ADD CONSTRAINT [PK_ROOM_STATUS] PRIMARY KEY NONCLUSTERED
(
[HOTE_ID], [ROST_ID]
) WITH FILLFACTOR = 90 ON [DEFAULT]
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

SET @msg = 'ROOM_STATUS created'
PRINT @msg
END
ELSE
BEGIN
-- TFS 9344 CONVERT TO UNICODE
IF EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_USER_CREA' AND USER_TYPE_ID =TYPE_ID(N'T_label20'))
AND EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'ROOM_STATUS') AND NAME = N'ROST_USER_CHANGE' AND USER_TYPE_ID =TYPE_ID(N'T_label20'))
BEGIN
PRINT 'DROP INDEX OF ROOM_STATUS';

DECLARE @IDX_NAME NVARCHAR(255);
DECLARE @TABLE_NAME NVARCHAR(255);
DECLARE @IS_UNIQUE BIT;
DECLARE @IS_CLUSTERED BIT;
DECLARE @COL_NAME NVARCHAR(MAX);
DECLARE @COL_INCLUDE NVARCHAR(MAX);
DECLARE @SQL_EXEC NVARCHAR(MAX);

CREATE TABLE #TABINDEX(TABLE_NAME NVARCHAR(50), INDEX_NAME NVARCHAR(255), COLUNM_NAME NVARCHAR(MAX), COLUNM_INCLUDE NVARCHAR(MAX),IS_UNIQUE BIT DEFAULT (0), IS_CLUSTERED BIT DEFAULT (0));

DECLARE CURIDX CURSOR FAST_FORWARD FOR SELECT I.NAME, OBJECT_NAME(I.OBJECT_ID),I.IS_UNIQUE,CASE WHEN I.TYPE = 2 THEN 0 ELSE 1 END FROM SYS.INDEXES I INNER JOIN SYS.TABLES T ON I.OBJECT_ID = T.OBJECT_ID AND T.IS_MS_SHIPPED =0
WHERE IS_PRIMARY_KEY =0
AND I.IS_UNIQUE_CONSTRAINT =0
AND I.NAME IS NOT NULL
AND T.NAME = 'ROOM_STATUS' ;

OPEN CURIDX;
FETCH NEXT FROM CURIDX INTO @IDX_NAME, @TABLE_NAME, @IS_UNIQUE, @IS_CLUSTERED
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @COL_INCLUDE=NULL;
SET @COL_NAME=NULL;

SELECT @COL_NAME=COALESCE(@COL_NAME + ',' + COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID) + ' ' + CASE WHEN IC.IS_DESCENDING_KEY =0 THEN 'ASC' ELSE 'DESC' END ,COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID) + ' ' + CASE WHEN IC.IS_DESCENDING_KEY =0 THEN 'ASC' ELSE 'DESC' END )
FROM SYS.INDEX_COLUMNS AS IC
INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = IC.OBJECT_ID AND I.INDEX_ID = IC.INDEX_ID
WHERE I.IS_PRIMARY_KEY =0
AND I.IS_UNIQUE_CONSTRAINT =0
AND IC.IS_INCLUDED_COLUMN =0
AND I.NAME = @IDX_NAME
ORDER BY IC.KEY_ORDINAL;

SELECT @COL_INCLUDE=COALESCE(@COL_INCLUDE + ',' + COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID),COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID))
FROM SYS.INDEX_COLUMNS AS IC
INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = IC.OBJECT_ID AND I.INDEX_ID = IC.INDEX_ID
WHERE I.IS_PRIMARY_KEY =0
AND I.IS_UNIQUE_CONSTRAINT =0
AND IC.IS_INCLUDED_COLUMN =1
AND I.NAME = @IDX_NAME
ORDER BY IC.KEY_ORDINAL;
INSERT INTO #TABINDEX(TABLE_NAME,INDEX_NAME,COLUNM_NAME, COLUNM_INCLUDE,IS_UNIQUE,IS_CLUSTERED )
VALUES (@TABLE_NAME,@IDX_NAME,@COL_NAME,@COL_INCLUDE,@IS_UNIQUE,@IS_CLUSTERED);

END
FETCH NEXT FROM CURIDX INTO @IDX_NAME, @TABLE_NAME, @IS_UNIQUE, @IS_CLUSTERED
END

CLOSE CURIDX;
DEALLOCATE CURIDX;
DECLARE CURTAB_DELETE CURSOR FAST_FORWARD FOR SELECT TABLE_NAME,INDEX_NAME,IS_UNIQUE,IS_CLUSTERED FROM #TABINDEX;

OPEN CURTAB_DELETE;

FETCH NEXT FROM CURTAB_DELETE INTO @TABLE_NAME,@IDX_NAME, @IS_UNIQUE, @IS_CLUSTERED;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @SQL_EXEC=N'DROP INDEX [' + @IDX_NAME +'] ON [DBO].[' + @TABLE_NAME + ']';
PRINT @SQL_EXEC;
EXEC(@SQL_EXEC);
END
FETCH NEXT FROM CURTAB_DELETE INTO @TABLE_NAME,@IDX_NAME, @IS_UNIQUE, @IS_CLUSTERED;
END

CLOSE CURTAB_DELETE;
DEALLOCATE CURTAB_DELETE;



SET @MSG = N'CHANGE THE TYPE OF THE ROST_USER_CREA COLUMN';
PRINT @MSG;
ALTER TABLE ROOM_STATUS ALTER COLUMN ROST_USER_CREA [t_nlabel20] NULL;
SET @SQLERROR=@@ERROR;
IF @SQLERROR <> 0 GOTO ERRORHANDLER;
SET @MSG = N'CHANGE THE TYPE OF THE ROST_USER_CHANGE COLUMN';
PRINT @MSG;
ALTER TABLE ROOM_STATUS ALTER COLUMN ROST_USER_CHANGE [t_nlabel20] NULL;
SET @SQLERROR=@@ERROR;
IF @SQLERROR <> 0 GOTO ERRORHANDLER;

PRINT 'CREATE THE INDEX OF ROOM_STATUS';
DECLARE CURTAB_CREATE CURSOR FAST_FORWARD FOR SELECT TABLE_NAME,INDEX_NAME,COLUNM_NAME,COLUNM_INCLUDE,IS_UNIQUE,IS_CLUSTERED FROM #TABINDEX;

OPEN CURTAB_CREATE;

FETCH NEXT FROM CURTAB_CREATE INTO @TABLE_NAME,@IDX_NAME,@COL_NAME, @COL_INCLUDE, @IS_UNIQUE, @IS_CLUSTERED;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @SQL_EXEC='CREATE ' + CASE WHEN @IS_UNIQUE = 1 THEN 'UNIQUE ' ELSE '' END + CASE WHEN @IS_CLUSTERED = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END + 'INDEX [' + @IDX_NAME +'] ON [DBO].[' + @TABLE_NAME + ']
(
' + @COL_NAME + '
)' + CASE WHEN @COL_INCLUDE IS NOT NULL THEN ' INCLUDE (' + @COL_INCLUDE + ')' ELSE '' END + ' ON [DEFAULT];'
PRINT @SQL_EXEC;
EXEC(@SQL_EXEC);
END
FETCH NEXT FROM CURTAB_CREATE INTO @TABLE_NAME,@IDX_NAME,@COL_NAME, @COL_INCLUDE, @IS_UNIQUE, @IS_CLUSTERED;
END

CLOSE CURTAB_CREATE;
DEALLOCATE CURTAB_CREATE;
DROP TABLE #TABINDEX;
END
ELSE
BEGIN
SET @MSG = 'THE COLUMN OF THE ROOM_STATUS TABLE IS UNICODE.';
PRINT @MSG;
END;
END

--
-- DEFINE TRIGGER
--
-- DEFINE tg_ROOM_STATUS_UPD
--
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[tg_ROOM_STATUS_UPD]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tg_ROOM_STATUS_UPD]
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

EXEC('CREATE TRIGGER tg_ROOM_STATUS_UPD
ON ROOM_STATUS
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
Update ROOM_STATUS
Set ROST_DATE_CHANGE=getdate()
FROM ROOM_STATUS inner join Inserted
on ROOM_STATUS.ROST_id = Inserted.ROST_id
And ROOM_STATUS.hote_id=Inserted.hote_id
END')
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[tg_ROOM_STATUS_RCLCA_ID_UPD]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tg_ROOM_STATUS_RCLCA_ID_UPD]
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

EXEC('CREATE TRIGGER tg_ROOM_STATUS_RCLCA_ID_UPD
ON ROOM_STATUS
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
Update ROOM_STATUS
Set RCLCA_ID=2
FROM ROOM_STATUS inner join Inserted
on ROOM_STATUS.RCLCA_ID = Inserted.RCLCA_ID
And ROOM_STATUS.hote_id=Inserted.hote_id
And Inserted.RCLCA_ID = 0
END')
SET @SQLError=@@ERROR
IF @SQLError <> 0 GOTO ERRORHANDLER

COMMIT TRAN
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
-- ===========================================================================================
-- Error Management
-- ===========================================================================================

ERRORHANDLER:
IF @SQLError <> 0 OR @@ERROR <> 0
BEGIN
IF (@@trancount > 0) ROLLBACK TRAN
RAISERROR (' Error in the script STR_ROOM_STATUS.sql', 16, -1)
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO