CREATE TABLE [BSERVER] (
	[SVS_ID] [int] NULL ,
	[SVS_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_SMO_ID] [int] NULL ,
	[SVS_CPU_ID] [int] NULL ,
	[SVS_MEY_ID] [int] NULL ,
	[SVS_HAD_ID] [int] NULL ,
	[SVS_ATY_ID] [int] NULL ,
	[SVS_OS_ID] [int] NULL ,
	[SVS_STORAGE] [int] NULL ,
	[SVS_SN] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_SHORTDESC] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_IPADDRESS] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_CREATE_DT] [datetime] NULL ,
	[SVS_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [BSVSBACKUP] (
	[SVS_ID] [int] NULL ,
	[SVS_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_SMO_ID] [int] NULL ,
	[SVS_CPU_ID] [int] NULL ,
	[SVS_MEY_ID] [int] NULL ,
	[SVS_HAD_ID] [int] NULL ,
	[SVS_ATY_ID] [int] NULL ,
	[SVS_OS_ID] [int] NULL ,
	[SVS_SN] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_SHORTDESC] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_IPADDRESS] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_OPERATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SVS_OPERATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [LSVSPOPEDOM] (
	[POP_ID] [int] NULL ,
	[POP_USR_ID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[POP_LIST_ADD] [bit] NULL ,
	[POP_LIST_MODIFY] [bit] NULL ,
	[POP_LIST_QUERY] [bit] NULL ,
	[POP_LIST_DEL] [bit] NULL ,
	[POP_LIST_DOWNLOAD] [bit] NULL ,
	[POP_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[POP_CREATE_DT] [datetime] NULL ,
	[POP_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[POP_UPDATE_DT] [datetime] NULL ,
	[POP_UPLOAD_ADD] [bit] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [RATYTYPE] (
	[ATY_ID] [int] NULL ,
	[ATY_NAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ATY_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[ATY_CREATE_DT] [datetime] NULL ,
	[ATY_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[ATY_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [RCPUTYPE] (
	[CPU_ID] [int] NULL ,
	[CPU_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[CPU_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[CPU_CREATE_DT] [datetime] NULL ,
	[CPU_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[CPU_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [RHADTYPE] (
	[HAD_ID] [int] NULL ,
	[HAD_NAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[HAD_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[HAD_CREATE_DT] [datetime] NULL ,
	[HAD_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[HAD_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [RMEYTYPE] (
	[MEY_ID] [int] NULL ,
	[MEY_NAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[MEY_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[MEY_CREATE_DT] [datetime] NULL ,
	[MEY_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[MEY_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [ROSTYPE] (
	[OS_ID] [int] NULL ,
	[OS_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[OS_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[OS_CREATE_DT] [datetime] NULL ,
	[OS_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[OS_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [RSERBACKUP] (
	[SER_SVS_ID] [int] NULL ,
	[SER_COME_DT] [datetime] NULL ,
	[SER_OWNER] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_PO_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_TEP_ID] [int] NULL ,
	[SER_STR_ID] [int] NULL ,
	[SER_AP_LIST] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_OPERATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_OPERATE_DT] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [RSERVER] (
	[SER_SVS_ID] [int] NULL ,
	[SER_COME_DT] [datetime] NULL ,
	[SER_OWNER] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_PO_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_TEP_ID] [int] NULL ,
	[SER_STR_ID] [int] NULL ,
	[SER_AP_LIST] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_OPERATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SER_OPERATE_DT] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [RSMOTYPE] (
	[SMO_ID] [int] NULL ,
	[SMO_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[SMO_CREATIONUID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SMO_CREATE_DT] [datetime] NULL ,
	[SMO_UPDATEUID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SMO_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [RSTRTYPE] (
	[STR_ID] [int] NULL ,
	[STR_NAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[STR_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[STR_CREATE_DT] [datetime] NULL ,
	[STR_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[STR_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [RTEAMTYPE] (
	[TEP_ID] [int] NULL ,
	[TEP_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[TEP_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[TEP_CREATE_DT] [datetime] NULL ,
	[TEP_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[TEP_UPDATE_DT] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [SSEQUENCE] (
	[SEQ_NUM] [int] NOT NULL ,
	[SEQ_TABLE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SEQ_STEP] [int] NOT NULL ,
	[SEQ_CREATIONUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SEQ_CREATE_DT] [datetime] NULL ,
	[SEQ_UPDATEUID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[SEQ_UPDATE_DT] [datetime] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[SEQ_TABLE]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE PORC_LSVSPOPEDOM_ADD
@POP_ID INT OUTPUT,
@POP_USR_ID varchar(5),
@POP_LIST_ADD bit,
@POP_LIST_MODIFY bit,
@POP_LIST_QUERY bit,
@POP_LIST_DEL BIT,
@POP_LIST_DOWNLOAD bit,
@POP_CREATIONUID varchar(5),
@POP_CREATE_DT datetime

 AS 
        DECLARE @ID INT
        IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'LSVSPOPEDOM')   
	BEGIN
		SET @POP_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'LSVSPOPEDOM') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'LSVSPOPEDOM' 
		
	INSERT INTO [LSVSPOPEDOM](
	[POP_ID],[POP_LIST_DEL],[POP_USR_ID],[POP_LIST_ADD],[POP_LIST_MODIFY],[POP_LIST_QUERY],[POP_LIST_DOWNLOAD],[POP_CREATIONUID],[POP_CREATE_DT] 
	)VALUES(
	@POP_ID,@POP_LIST_DEL,@POP_USR_ID,@POP_LIST_ADD,@POP_LIST_MODIFY,@POP_LIST_QUERY,@POP_LIST_DOWNLOAD,@POP_CREATIONUID,getdate()  
	)
--PRINT  'Ȩ޼¼ID='+@ID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO








CREATE           PROCEDURE   PROC_BSERVER_ADD
@SVS_ID int output,
@SVS_NAME varchar(50),
@SVS_SMO_ID int,
@SVS_CPU_ID int,
@SVS_MEY_ID int,
@SVS_HAD_ID int,
@SVS_ATY_ID int,
@SVS_OS_ID int,
@SVS_SN varchar(50),
@SVS_SHORTDESC varchar(100),
@SVS_IPADDRESS varchar(15),
@SVS_CREATIONUID varchar(5)
AS
IF  EXISTS(SELECT * FROM BSERVER WHERE SVS_NAME = @SVS_NAME)
BEGIN
     SET  @SVS_ID= (SELECT SVS_ID FROM BSERVER WHERE SVS_NAME = @SVS_NAME) 
     RETURN 
END
IF   EXISTS(SELECT * FROM SSEQUENCE WHERE SEQ_TABLE = 'BSERVER')   
BEGIN     
	SET @SVS_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'BSERVER') 
	UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'BSERVER' 
	INSERT INTO [BSERVER](
	[SVS_ID],[SVS_NAME],[SVS_SMO_ID],[SVS_CPU_ID],[SVS_MEY_ID],[SVS_HAD_ID],[SVS_ATY_ID],[SVS_OS_ID],[SVS_SN],[SVS_SHORTDESC],[SVS_IPADDRESS],[SVS_CREATIONUID],[SVS_CREATE_DT]
	)VALUES(
	@SVS_ID,@SVS_NAME,@SVS_SMO_ID,@SVS_CPU_ID,@SVS_MEY_ID,@SVS_HAD_ID,@SVS_ATY_ID,@SVS_OS_ID,@SVS_SN,@SVS_SHORTDESC,@SVS_IPADDRESS,@SVS_CREATIONUID,GETDATE() 
	)
	
END









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE     PROCEDURE PROC_BSERVER_UPDATE
@SVS_ID int,
@SVS_NAME varchar(50),
@SVS_SMO_ID int,
@SVS_CPU_ID int,
@SVS_MEY_ID int,
@SVS_HAD_ID int,
@SVS_ATY_ID int,
@SVS_OS_ID int,
@SVS_SN varchar(50),
@SVS_SHORTDESC varchar(100),
@SVS_IPADDRESS varchar(15),
@SVS_UPDATEUID varchar(5)
 AS 

   
	UPDATE [BSERVER] SET 
	[SVS_ID] = @SVS_ID,[SVS_NAME] = @SVS_NAME,[SVS_SMO_ID] = @SVS_SMO_ID,[SVS_CPU_ID] = @SVS_CPU_ID,
        [SVS_MEY_ID] = @SVS_MEY_ID,[SVS_HAD_ID] = @SVS_HAD_ID,[SVS_ATY_ID] = @SVS_ATY_ID,[SVS_OS_ID] = @SVS_OS_ID,
        [SVS_SN] = @SVS_SN,[SVS_SHORTDESC] = @SVS_SHORTDESC,[SVS_IPADDRESS] = @SVS_IPADDRESS,
        [SVS_UPDATEUID] = @SVS_UPDATEUID,[SVS_UPDATE_DT] = GETDATE()
	WHERE SVS_ID=@SVS_ID

         INSERT INTO BSVSBACKUP  SELECT SVS_ID,SVS_NAME, SVS_SMO_ID,SVS_CPU_ID,SVS_MEY_ID,
         SVS_HAD_ID,SVS_ATY_ID,SVS_OS_ID,SVS_SN,SVS_SHORTDESC,SVS_IPADDRESS,SVS_UPDATEUID,SVS_UPDATE_DT   
	 FROM BSERVER WHERE SVS_ID= @SVS_ID 


SELECT * FROM BSVSBACKUP



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


-- ݱҳ洢
CREATE            PROCEDURE PROC_GET_SPLIT
	@TABLENAME VARCHAR(255), --  
	@FIELDSCOLUMN VARCHAR(1000) = '*', -- Ҫص 
	@SORTFIELDS VARCHAR(255)='', -- ֶ 
	@PAGESIZE INT = 10, -- ҳߴ 
	@PAGEINDEX INT = 1, -- ҳ 
	@TOTALCOUNT BIT = 0, -- ؼ¼,  0 ֵ򷵻 
	@ORDERTYPE BIT = 0, -- ,  0 ֵ 
	@WHERE VARCHAR(1500) = '' -- ѯ (ע: Ҫ where) 
AS 
	DECLARE @strSQL VARCHAR(5000) --  
	DECLARE @strTmp VARCHAR(110) -- ʱ 
	DECLARE @strOrder VARCHAR(400) --  
		IF @TOTALCOUNT != 0  
		BEGIN
			IF @WHERE !='' 
				IF @FIELDSCOLUMN!='*'
					SET @strSQL = 'SELECT COUNT(DISTINCT '+@FIELDSCOLUMN+') as TOTAL FROM [' + @TABLENAME + '] WHERE '+@WHERE 
				ELSE
					SET @strSQL = 'SELECT COUNT('+@FIELDSCOLUMN+') as TOTAL FROM [' + @TABLENAME + '] WHERE '+@WHERE 
			ELSE 
				IF @FIELDSCOLUMN!='*'
					SET @strSQL = 'SELECT COUNT(DISTINCT '+@FIELDSCOLUMN+') as TOTAL FROM [' + @TABLENAME + ']' 
				ELSE
					SET @strSQL = 'SELECT COUNT('+@FIELDSCOLUMN+') as TOTAL FROM [' + @TABLENAME + ']'
		END 
			--ϴ˼@TOTALCOUNTݹĲ0ִͳơµд붼@TOTALCOUNTΪ0 
		ELSE 
		BEGIN 
			IF @ORDERTYPE!= 0
				BEGIN 
				SET @strTmp = '<(SELECT MIN' 
				SET @strOrder = ' ORDER BY [' + @SORTFIELDS +'] DESC' 
				--@ORDERTYPE0ִнҪ 
				END 
			ELSE 
				BEGIN 
				SET @strTmp = '>(SELECT MAX' 
				SET @strOrder = ' ORDER BY [' + @SORTFIELDS +'] ASC' 
				END 
			
			IF @PAGEINDEX = 1 
				BEGIN 
				IF @WHERE != '' 
					SET @strSQL = 'SELECT DISTINCT TOP ' + str(@PAGESIZE) +' '+@FIELDSCOLUMN+ ' FROM [' + @TABLENAME + '] WHERE ' + @WHERE + ' ' + @strOrder 
				ELSE 
					SET @strSQL = 'SELECT DISTINCT TOP ' + str(@PAGESIZE) +' '+@FIELDSCOLUMN+ ' FROM ['+ @TABLENAME + '] '+ @strOrder 
					--ǵһҳִϴ룬ӿִٶ 
				END 
			ELSE 
				BEGIN 
				--´븳@strSQLִеSQL 
				set @strSQL = 'SELECT DISTINCT TOP ' + str(@PAGESIZE) +' '+@FIELDSCOLUMN+ ' FROM [' + @TABLENAME + '] 
				WHERE [' + @SORTFIELDS + ']' + @strTmp + '(['+ @SORTFIELDS + ']) 
				FROM (SELECT DISTINCT TOP ' + str((@PAGEINDEX-1)*@PAGESIZE) + ' ['+ @SORTFIELDS + '] 
				FROM [' + @TABLENAME + ']' + @strOrder + ') 
				AS  TBLTMP)'+ @strOrder 
				if @WHERE != '' 
					set @strSQL = 'SELECT DISTINCT TOP ' + str(@PAGESIZE) +' '+@FIELDSCOLUMN+ ' FROM [' + @TABLENAME + '] 
					WHERE [' + @SORTFIELDS + ']' + @strTmp + '([' + @SORTFIELDS + ']) 
					FROM (SELECT DISTINCT TOP ' + str((@PAGEINDEX-1)*@PAGESIZE) + ' [' + @SORTFIELDS + '] 
					FROM [' + @TABLENAME + '] 
					WHERE ' + @WHERE + ' ' + @strOrder + ') 
					AS TBLTMP) AND ' + @WHERE + ' ' + @strOrder 
				END 
		END 
	EXEC (@strSQL) 






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE      PROCEDURE PROC_RATYTYPE_ADD
@ATY_ID int OUTPUT,
@ATY_NAME varchar(10),
@ATY_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RATYTYPE WHERE ATY_NAME=@ATY_NAME)
        BEGIN
             SET @ATY_ID=(SELECT MIN(ATY_ID) FROM RATYTYPE WHERE ATY_NAME=@ATY_NAME GROUP BY ATY_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RATYTYPE')   
	BEGIN
		SET @ATY_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RATYTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RATYTYPE' 
		
	INSERT INTO RATYTYPE(
	[ATY_ID],[ATY_NAME],[ATY_CREATE_DT],[ATY_CREATIONUID] 
	)VALUES(
	@ATY_ID,@ATY_NAME,getdate(),@ATY_CREATIONUID)
     END









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE       PROCEDURE PROC_RCPUTYPE_ADD
@CPU_ID int OUTPUT,
@CPU_NAME varchar(100),
@CPU_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RCPUTYPE WHERE CPU_NAME=@CPU_NAME)
        BEGIN
             SET @CPU_ID=(SELECT MIN(CPU_ID) FROM RCPUTYPE WHERE CPU_NAME=@CPU_NAME GROUP BY CPU_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RCPUTYPE')   
	BEGIN
		SET @CPU_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RCPUTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RCPUTYPE' 
		
	INSERT INTO RCPUTYPE(
	[CPU_ID],[CPU_NAME],[CPU_CREATE_DT],[CPU_CREATIONUID] 
	)VALUES(
	@CPU_ID,@CPU_NAME,getdate(),@CPU_CREATIONUID)
     END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE       PROCEDURE PROC_RHADTYPE_ADD
@HAD_ID int OUTPUT,
@HAD_NAME varchar(30),
@HAD_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RHADTYPE WHERE HAD_NAME=@HAD_NAME)
        BEGIN
             SET @HAD_ID=(SELECT MIN(HAD_ID) FROM RHADTYPE WHERE HAD_NAME=@HAD_NAME GROUP BY HAD_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RHADTYPE')   
	BEGIN
		SET @HAD_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RHADTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RHADTYPE' 
		
	INSERT INTO RHADTYPE(
	[HAD_ID],[HAD_NAME],[HAD_CREATE_DT],[HAD_CREATIONUID] 
	)VALUES(
	@HAD_ID,@HAD_NAME,getdate(),@HAD_CREATIONUID)
     END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE      PROCEDURE PROC_RMEYTYPE_ADD
@MEY_ID int OUTPUT,
@MEY_NAME varchar(30),
@MEY_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RMEYTYPE WHERE MEY_NAME=@MEY_NAME)
        BEGIN
             SET @MEY_ID=(SELECT MIN(MEY_ID) FROM RMEYTYPE WHERE MEY_NAME=@MEY_NAME GROUP BY MEY_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RMEYTYPE')   
	BEGIN
		SET @MEY_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RMEYTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RMEYTYPE' 
		
	INSERT INTO RMEYTYPE(
	[MEY_ID],[MEY_NAME],[MEY_CREATE_DT],[MEY_CREATIONUID] 
	)VALUES(
	@MEY_ID,@MEY_NAME,getdate(),@MEY_CREATIONUID)
     END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE      PROCEDURE PROC_ROSTYPE_ADD
@OS_ID int OUTPUT,
@OS_NAME varchar(50),
@OS_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM ROSTYPE WHERE OS_NAME=@OS_NAME)
        BEGIN
             SET @OS_ID=(SELECT MIN(OS_ID) FROM ROSTYPE WHERE OS_NAME=@OS_NAME GROUP BY OS_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'ROSTYPE')   
	BEGIN
		SET @OS_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'ROSTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'ROSTYPE' 
		
	INSERT INTO ROSTYPE(
	[OS_ID],[OS_NAME],[OS_CREATE_DT],[OS_CREATIONUID] 
	)VALUES(
	@OS_ID,@OS_NAME,getdate(),@OS_CREATIONUID)
     END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE       PROCEDURE   PROC_RSERVER_UPDATE
@SER_SVS_ID INT,
@SER_COME_DT DATETIME,
@SER_OWNER VARCHAR(50),
@SER_PO_NO  VARCHAR(50),
@SER_TEP_ID INT,
@SER_STR_ID INT,
@SER_AP_LIST TEXT,
@SER_OPERATEUID  VARCHAR(5),
@SER_OPERATE_DT  DATETIME

AS

BEGIN

DELETE FROM RSERVER WHERE SER_SVS_ID=@SER_SVS_ID 
INSERT INTO [RSERVER](
	[SER_SVS_ID],[SER_COME_DT],[SER_OWNER],[SER_PO_NO],[SER_TEP_ID],[SER_STR_ID],[SER_AP_LIST],[SER_OPERATEUID],[SER_OPERATE_DT] 
	)VALUES(
	@SER_SVS_ID ,@SER_COME_DT ,@SER_OWNER ,@SER_PO_NO,@SER_TEP_ID ,@SER_STR_ID ,@SER_AP_LIST ,@SER_OPERATEUID ,@SER_OPERATE_DT
)
INSERT INTO RSERBACKUP (
[SER_SVS_ID],[SER_COME_DT],[SER_OWNER],[SER_PO_NO],[SER_TEP_ID],[SER_STR_ID],[SER_AP_LIST],[SER_OPERATEUID],[SER_OPERATE_DT] 
	)VALUES(
	@SER_SVS_ID ,@SER_COME_DT ,@SER_OWNER ,@SER_PO_NO,@SER_TEP_ID ,@SER_STR_ID ,@SER_AP_LIST ,@SER_OPERATEUID ,@SER_OPERATE_DT
)


END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE        PROCEDURE PROC_RSMOTYPE_ADD
@SMO_ID int OUTPUT,
@SMO_NAME varchar(50),
@SMO_CREATIONUID varchar(5)
 AS 
        IF EXISTS(SELECT * FROM RSMOTYPE WHERE SMO_NAME=@SMO_NAME)
        BEGIN
             SET @SMO_ID=(SELECT MIN(SMO_ID) FROM RSMOTYPE WHERE SMO_NAME=@SMO_NAME GROUP BY SMO_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RSMOTYPE')   
	BEGIN
		SET @SMO_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RSMOTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RSMOTYPE' 
		
	INSERT INTO RSMOTYPE(
	[SMO_ID],[SMO_NAME],[SMO_CREATE_DT],[SMO_CREATIONUID] 
	)VALUES(
	@SMO_ID,@SMO_NAME,getdate(),@SMO_CREATIONUID)
     END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE       PROCEDURE PROC_RSTRTYPE_ADD
@STR_ID int OUTPUT,
@STR_NAME varchar(100),
@STR_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RSTRTYPE WHERE STR_NAME=@STR_NAME)
        BEGIN
             SET @STR_ID=(SELECT MIN(STR_ID) FROM RSTRTYPE WHERE STR_NAME=@STR_NAME GROUP BY STR_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RSTRTYPE')   
	BEGIN
		SET @STR_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RSTRTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RSTRTYPE' 
		
	INSERT INTO RSTRTYPE(
	[STR_ID],[STR_NAME],[STR_CREATE_DT],[STR_CREATIONUID] 
	)VALUES(
	@STR_ID,@STR_NAME,getdate(),@STR_CREATIONUID)
     END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE        PROCEDURE PROC_RTEAMTYPE_ADD
@TEP_ID int OUTPUT,
@TEP_NAME varchar(100),
@TEP_CREATIONUID varchar(5)

 AS 
        IF EXISTS(SELECT * FROM RTEAMTYPE WHERE TEP_NAME=@TEP_NAME)
        BEGIN
             SET @TEP_ID=(SELECT MIN(TEP_ID) FROM RTEAMTYPE WHERE TEP_NAME=@TEP_NAME GROUP BY TEP_NAME)
              return
	END
	IF   EXISTS(SELECT * FROM   SSEQUENCE WHERE SEQ_TABLE = 'RTEAMTYPE')   
	BEGIN
		SET @TEP_ID= (SELECT SEQ_NUM FROM SSEQUENCE WHERE SEQ_TABLE = 'RTEAMTYPE') 
		UPDATE SSEQUENCE SET SEQ_NUM = SEQ_NUM + SEQ_STEP, SEQ_UPDATE_DT = GETDATE(),SEQ_UPDATEUID ='SYS' WHERE SEQ_TABLE = 'RTEAMTYPE' 
		
	INSERT INTO RTEAMTYPE(
	[TEP_ID],[TEP_NAME],[TEP_CREATE_DT],[TEP_CREATIONUID] 
	)VALUES(
	@TEP_ID,@TEP_NAME,getdate(),@TEP_CREATIONUID)
     END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE    VIEW VM_SERVER_MODEL
AS
SELECT DISTINCT A.SVS_ID,A.SVS_NAME,A.SVS_IPADDRESS,A.SVS_SHORTDESC,A.SVS_SN,
B.SMO_ID,B.SMO_NAME,
C.CPU_ID,C.CPU_NAME,
D.MEY_ID,D.MEY_NAME,
E.HAD_ID,E.HAD_NAME,
F.ATY_ID,F.ATY_NAME,
G.OS_ID,G.OS_NAME 
 FROM  
BSERVER AS A LEFT JOIN RSMOTYPE AS B ON A.SVS_SMO_ID=B.SMO_ID
INNER JOIN RCPUTYPE AS C ON A.SVS_CPU_ID=C.CPU_ID
INNER JOIN RMEYTYPE AS D ON A.SVS_MEY_ID=D.MEY_ID
INNER JOIN RHADTYPE AS E ON A.SVS_HAD_ID=E.HAD_ID
INNER JOIN RATYTYPE AS F ON A.SVS_ATY_ID=F.ATY_ID  
INNER JOIN ROSTYPE AS G ON A.SVS_OS_ID=G.OS_ID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO














