

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/AppStudioDB/Sp/QS_InsertIntoWFExtendedPropertiesTable.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
--
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='QS_InsertIntoWFExtendedPropertiesTable')
BEGIN
	print '>>> Drop Stored Procedure: QS_InsertIntoWFExtendedPropertiesTable <<<'
	drop procedure QS_InsertIntoWFExtendedPropertiesTable
END
IF EXISTS (select * from GxQscripts where name='QS_InsertIntoWFExtendedPropertiesTable')
	delete from GxQscripts where name = 'QS_InsertIntoWFExtendedPropertiesTable'
GO

IF EXISTS (select * from GXDBVersions where aliasname='QS_InsertIntoWFExtendedPropertiesTable')
	delete from GXDBVersions where aliasname = 'QS_InsertIntoWFExtendedPropertiesTable'
GO
print '... Creating Procedure: QS_InsertIntoWFExtendedPropertiesTable'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure QS_InsertIntoWFExtendedPropertiesTable
-- Qscript is Enabled
-- Set Audit Level: NO_AUDIT_TRAIL, AUDIT_TRAIL_LEVEL_LOW, _MEDIUM, _HIGH, _CRITICAL
-- Qscript Usage. Do not use special char in HTML
-- SP Input Params
  @ClientHostName nvarchar(512),
  @DatasourceName nvarchar(512),
  @SharePath nvarchar(max)=NULL,
  @Country nvarchar(255)=NULL,
  @UserName nvarchar(255)=NULL,
  @Password nvarchar(255)=NULL,
  @DCPlan nvarchar(255)=NULL,
  @AccessNode nvarchar(255)=NULL,
  @ArchivingPlan nvarchar(255)=NULL, 
  @BackedupStatus int=0,
  @CrawlType int=0,
  @CreatedTime int = NULL,
  @CustomPropName nvarchar(max)=NULL,
  @CustomPropValue nvarchar(max)=NULL
AS
Set NOCOUNT ON
------------ Convenience Definitions -----------
    DECLARE @scriptName varchar(64)
    SET     @scriptName = 'QS_InsertIntoWFExtendedPropertiesTable'
	DECLARE @lastError  integer
    SET     @lastError  = 0
    DECLARE @localErrorMsg      varchar(512)
    SET     @localErrorMsg          =''
	IF OBJECT_ID('tempdb.dbo.##CProp') is not null
    		DROP TABLE ##CProp
	CREATE TABLE ##CProp( name nvarchar(max),value nvarchar(max))
	IF @CustomPropName = ''
		SET @CustomPropName = NULL
	IF @CustomPropValue = ''
		SET @CustomPropValue = NULL
------------ Check if Input Table Exists -----------
	DECLARE @input_table nvarchar(64) = N'AppStudioDB.dbo.WF_Extended_Properties'
	IF OBJECT_ID ('AppStudioDB.dbo.WF_Extended_Properties', 'U') IS NULL
		BEGIN
			SET @lastError = 2
			SET @localErrorMsg = @input_table + ' Table Not Found'
			GOTO PROC_ERROR
		END
	IF (@CustomPropName <> '' and @CustomPropName IS NOT NULL) AND (@CustomPropValue <> '' and @CustomPropValue IS NOT NULL)
	IF ((select count(item) FROM AZ_StringSplitFunction(@CustomPropName,';')) - (select count(item) FROM AZ_StringSplitFunction(@customPropValue,';'))) <> 0
		BEGIN
			SET @lastError = 3
			SET @localErrorMsg = 'Custom Properties Name Count Does Not Match with count of values provided'
			GOTO PROC_ERROR
		END
------------- Insert Into Table ---------------
	DECLARE @i_name nvarchar(max)
	DECLARE @i_value nvarchar(max)
	BEGIN TRAN
	BEGIN TRY
		INSERT ##CProp
		select A.item,B.item
		from(
		SELECT item,row_number() over (order by (select 0)) as row_num
		FROM AZ_StringSplitFunction(@CustomPropName,';'))A
		join
		(SELECT item,row_number() over (order by (select 0)) as row_num
		FROM AZ_StringSplitFunction(@CustomPropValue,';'))B
		on  A.row_num=B.row_num
		SELECT @i_name = COALESCE(@i_name+',','') + QUOTENAME(name) from ##Cprop
		SELECT @i_value = COALESCE(@i_value+',','') + QUOTENAME(value) from ##Cprop
		DECLARE @selectDefault nvarchar(max) = N'SELECT '''+@ClientHostName+''' AS ClientHostName,'''
			+@DatasourceName+''' AS DatasourceName,'''
			+ISNULL(@SharePath,'')+''' AS SharePath,'''
			+ISNULL(@Country,'')+''' AS Country,'''
			+ISNULL(@UserName,'')+''' AS UserName,'''
			+ISNULL(@Password,'')+''' AS Password,'''
			+ISNULL(@DCPlan,'')+''' AS DCPlan,'''
			+ISNULL(@AccessNode,'')+''' AS AccessNode,'''
			+ISNULL(@ArchivingPlan,'')+''' AS ArchivingPlan,'''
			+CONVERT(varchar(8),@BackedupStatus)+''' AS BackedupStatus,'''
			+CONVERT(varchar(8),@CrawlType)+''' AS CrawlType,'''
			+CONVERT(varchar(32), ISNULL(@CreatedTime, commserv.dbo.GetUnixTime (GetUTCdate())))+''' AS Created'
		DECLARE @selectCustom nvarchar(max) = ','+ ISNULL(@i_name,'')+'
			FROM
			(
			SELECT 1 as id,name,value FROM ##CProp
			)Temp
			PIVOT (
			MAX(value)
			FOR  name in ('+ISNULL(@i_name,'')+')) AS Q'
		DECLARE @insertQueryDefault nvarchar(max) = 'INSERT INTO '+@input_table+'
				([ClientHostName]
				,[DatasourceName]
				,[SharePath]
				,[Country]
				,[UserName]
				,[Password]
				,[DCPlan]
				,[AccessNode]
				,[ArchivingPlan]
				,[BackedupStatus]
				,[CrawlType]
				,[Created]'
			IF (@CustomPropName IS NOT NULL) AND (@CustomPropValue IS NOT NULL)
				BEGIN
					SET @selectDefault = @selectDefault + @selectCustom
					SET @insertQueryDefault = @insertQueryDefault + ','+@i_name+') '+@selectDefault
				END
			ELSE
				SET @insertQueryDefault = @insertQueryDefault + ') ' + @selectDefault
		EXEC sp_executeSQl @insertQueryDefault
		IF (@@ERROR <> 0)
			BEGIN
				SET @lastError = @@ERROR
				SET @localErrorMsg = 'Failed to insert row to the table'
				GOTO ERROR_EXIT
			END
	END TRY
	BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
			SET @lastError = ERROR_NUMBER()
			SET @localErrorMsg = (SELECT ERROR_MESSAGE())
	END CATCH
ERROR_EXIT:
	IF @lastError <> 0
		ROLLBACK TRAN
	ELSE
		COMMIT TRAN
PROC_ERROR:
	if object_id('tempdb.dbo.##CProp') is not null
    	DROP TABLE ##CProp
    PRINT ''
    PRINT @scriptName + ' completed at '
                    + Convert(varchar(60), getdate())+' with parameters'
                    + ' ClientHostName ['+ @ClientHostName + '].'
                    + ' DatasourceName ['+ @DatasourceName + '].'
					+ ' SharePath ['+ ISNULL(@SharePath,'') + '].'
					+ ' Country ['+   ISNULL(@Country,'') + '].'
					+ ' UserName ['+  ISNULL(@UserName,'') + '].'
					+ ' Password ['+  ISNULL(@Password,'') + '].'
					+ ' DCPlan ['+    ISNULL(@DCPlan,'') + '].'
					+ ' AccessNode ['+ ISNULL(@AccessNode,'') + '].'
					+ ' ArchivingPlan ['+ ISNULL(@ArchivingPlan,'') + '].'
					+ ' BackedupStatus ['+ CONVERT(varchar(8),@BackedupStatus) + '].'
					+ ' CrawlType ['+ CONVERT(varchar(8),@CrawlType) + '].'
					+ ' CustomProp ('+ ISNULL(@i_name,'') + ').'
                    + ' ErrorCode (' + Convert(varchar(2),@lastError) +').'
					+ ' Error Message (' + @localErrorMsg +').'
    if @lastError <> 0
        Raiserror(@localErrorMsg,
                    15,  --Severity (> 10 to force DOS Error Code = 1)
                    1    --State
                    )
GO

IF EXISTS (select * from GxQscripts where name = 'QS_InsertIntoWFExtendedPropertiesTable')
	delete from GxQscripts where name = 'QS_InsertIntoWFExtendedPropertiesTable'
GO

IF EXISTS (select * from GXDBVersions where aliasname='QS_InsertIntoWFExtendedPropertiesTable')
	delete from GXDBVersions where aliasname = 'QS_InsertIntoWFExtendedPropertiesTable'
GO

insert into GXDBVersions values(2, 'QS_InsertIntoWFExtendedPropertiesTable',  '00000000000000000000', 'QS_InsertIntoWFExtendedPropertiesTable', '00000000000000000000')
GO

insert into GxQscripts values(2,'QS_InsertIntoWFExtendedPropertiesTable',  0, GETDATE(), GETDATE(), '' + CHAR(10) + '	qoperation execscript -sn QS_InsertIntoWFExtendedPropertiesTable -si ClientHostName -si DatasourceName 
'
 + CHAR(10) + '	Example: qoperation execscript -sn QS_InsertIntoWFExtendedPropertiesTable -si ClientHostName=''test.sd.commvault.com'' -si DatasourceName=''TestDS'' -si SharePath=''\\test.sd.commvault.com\testdata'' -si Country=''United States'' -si UserName=''sd\newuser'' -si Password=''VGVzdCExMg=='' -si DCPlan=''fso_dc_plan'' -si AccessNode=''testAccess'' -si ArchivingPlan=''CV_DEFAULT'' -si BackedupStatus=1 -si CrawlType=4 -si CustomPropName=''CustomProp_AU;CustomProp_BU'' -si CustomPropValue=''Test1,Test2;Test3,Test4''
'
 + CHAR(10) + '-- 	script_name: QS_InsertIntoWFExtendedPropertiesTable.'
 + CHAR(10) + '-- 	ClientHostName - (mandatory) Client Hostname .For Example ''test.sd.commvault.com'''
 + CHAR(10) + '-- 	DatasourceName - (mandatory) Datasource Name .For Example ''TestDS'''
 + CHAR(10) + '-- 	SharePath - Sharepath .For Example ''\\test.sd.commvault.com\testdata'''
 + CHAR(10) + '-- 	Country - Country .For Example ''United States'''
 + CHAR(10) + '-- 	UserName - Username .For Example ''sd\newuser'''
 + CHAR(10) + '-- 	Password - Base 64 Encoded Password .For Example ''VGVzdCExMg=='''
 + CHAR(10) + '-- 	DCPlan - Data classification Plan .For Example ''fso_dc_plan'''
 + CHAR(10) + '-- 	AccessNode - Access Node .'
 + CHAR(10) + '-- 	ArchivingPlan - Archiving Plan .For Example ''CV_DEFAULT'''
 + CHAR(10) + '-- 	BackedupStatus - Backed Up Status (0 - Backed Up ,1 - Live ) .For Example: 0'
 + CHAR(10) + '-- 	CrawlType - Crawl Type (1 - Live, 4 - Quick Scan)  .For Example: 4'
 + CHAR(10) + '-- 	CustomPropName - Example ''CustomProp_AU;CustomProp_BU'''
 + CHAR(10) + '-- 	CustomPropValue - Example ''Test1,Test2;Test3,Test4'''
)
GO

