DrivePool.sql

USE [DPH_BANK_ADMIN_MULTIPLE]
GO
/****** Object:  StoredProcedure [dbo].[DRIVEPOOL2]    Script Date: 21-07-2018 10:34:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--EXEC [dbo].[DRIVEPOOL] @OUT_MAIN_ERROR = ''

ALTER PROCEDURE [dbo].[DRIVEPOOL2]
  

  
AS 

-----Changes for SAN
 DECLARE @cnt INT = 1
 Declare @driveList varchar(500),
 @cmdline varchar (500),
 @ADJ_REPORT_RCODE int,
 @ServerDrives varchar (500),
 @SeparateDrives varchar(50),
 @individualDrives varchar(50),
 @Wholelength varchar(60),
  @COUNTTEMP int,
  @ReturnCode int,
     @OUT_MAIN_ERROR varchar(50) ,
	  @OPERATION varchar(10)='OPEN',
   @DRIVEVALUE varchar (10) = 'D' 

Declare @DriveCheck varchar(10)
Declare @PassDrive varchar(50)
Declare @Printin varchar(10)

 CREATE TABLE #temp (SomeCol VARCHAR(500) )
 
 --================ ADJ
SELECT @cmdline = 'fsutil fsinfo drives'
--select @cmdline ADJ
BEGIN
Set Nocount ON

--select @cmdline commands

IF @OPERATION = 'OPEN'
BEGIN

 
--dump result into temp table
INSERT #temp 
EXEC @ADJ_REPORT_RCODE = master.dbo.xp_cmdshell @cmdline 

select  @ServerDrives = SomeCol from #temp where SomeCol Like '%Drives%'
--select @ADJ_REPORT_RCODE kali



--select @ServerDrives Symbols

SELECT @ServerDrives =  REPLACE(@ServerDrives, ':\ ', '');

--select @ServerDrives withoutSymbols

SELECT @ServerDrives =  REPLACE(@ServerDrives, 'Drives: ', '');

SELECT @individualDrives =  REPLACE(@ServerDrives, ' ', '');


select @Wholelength = LEN(@individualDrives);


--select @Wholelength withoutSymbolsLength

WHILE @cnt <= @Wholelength
BEGIN
 --  PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';

   
 -- select @individualDrives KKKKKKK
-- select @cnt LLLLLL
    
   SELECT  @DriveCheck =  SUBSTRING(@individualDrives, @cnt,1) ;
 -- select @DriveCheck PPPPPPPP
   
   UPDATE [dbo].[Drive_Pool]  SET [Drives] = @DriveCheck ,[Status] = '1'
 WHERE [Drives] = @DriveCheck

 SET @cnt = @cnt + 1;

END;

 select top 1 @PassDrive =  Drives from [dbo].[Drive_Pool] where Drives <> @DriveCheck and Status = '0'

 --select @PassDrive kaliss

 --Return @PassDrive

   if @PassDrive IS NULL
      Begin

	  SET  @OUT_MAIN_ERROR='WAIT'
	  SELECT @OUT_MAIN_ERROR

 -- PRINT 'Done IF - Return wait and run';

   End
   Else 
   Begin

  -- PRINT 'Done Else - Found the Drive and Returning';



   -------------------------
   -----Changes for SAN

DECLARE @Text AS VARCHAR(100), @Cmd2 AS VARCHAR(100), @KeyGUID UNIQUEIDENTIFIER ,@open nvarchar(max) , @close nvarchar(max) , @cmd varbinary(max), @Data Varchar(50),
  @sanip VARCHAR(500),   @sanuser VARCHAR(500),
@sanlocation VARCHAR(500),  @sanpass VARCHAR(500),	@Question varbinary(max) , @checkguid varchar(500) , @Drive_Opener VARCHAR(50)


----Changes for SAN
   DECLARE  @NOOFDRIVE int = 1
-------DRIVE CODE



--Paramters for SAN
select @sanip =  SANIP from dbo.IPS_IPSCONFIG_MASTER;
select @sanuser =  SANUSERNAME from dbo.IPS_IPSCONFIG_MASTER;
select @sanlocation =  SANLOCATION from dbo.IPS_IPSCONFIG_MASTER;
select @Question =  SANPASSWORD from dbo.IPS_IPSCONFIG_MASTER;
select @checkguid =  KEY_GUID('TestKey') from sys.symmetric_keys;

--Paramters for SAN







   -------------------------
 --select  @OUT_MAIN_ERROR =  'N' 

 --SET  @OUT_MAIN_ERROR =  @PassDrive 

 Select @PassDrive 
  
   End

   
drop table #temp

END

ELSE

BEGIN


--PRINT 'Connection Going to be Closed';

   --Create temp table to hold result
CREATE TABLE #conclose (SomeCol VARCHAR(500))



--=============================ADJ
SELECT @cmdline = 'net use '+@DRIVEVALUE+': /delete /Y'
--select @cmdline polos
  
--dump result into temp table
INSERT #conclose
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
    


	SELECT @COUNTTEMP = COUNT(*)  FROM #conclose WHERE SomeCol like '%was deleted successfully.%';


IF @COUNTTEMP = '1'
	Begin
	 
	 SET  @OUT_MAIN_ERROR='OK'
 
 SELECT @OUT_MAIN_ERROR



	End
	Else
		Begin
		 SET  @OUT_MAIN_ERROR='ERR'
 
 SELECT @OUT_MAIN_ERROR

	End

--PRINT 'Done Else Successful return a Drive which is available';



END



Set Nocount OFF
END  --main