SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[adminMakeWorkingFolders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[adminMakeWorkingFolders]
GO


create procedure dbo.adminMakeWorkingFolders
        @pProjectName varchar(30),
	@pDatabaseName varchar(30) = '%'
as
/**********************************************************************
Procedure creates the standard file system folder hierarchy on a member 
server of the first level of the environment hierarchy (aka unit test).  
Hierarchies for all environments in the specified project on the server 
are created if no database name. If a database is specified, then only 
that database's hierarchy will be built. The project must be established 
in the admin metadata tables else the procedure will have nothing to do. 
Notes: Does not modify any existing folders. Does not populate any level. 
Environments other than unit test must use get latest, checkout 
or promote procedures to build thier hierarchy. mkdir creates all missing 
folders in the path.
***********************************************************************/
declare	@vssUserName varchar(30),
        @instanceName varchar(30),
	@serverName varchar(30),
	@databaseName varchar(30),
	@environmentName varchar(30),
	@prevEnvironmentName varchar(30),
	@workingFolderRoot varchar(100),
	@vssShare varchar(255),
	@vssHierarchyRoot varchar(255),
	@cmd nvarchar(1200),
	@returnstatus int,
	@procedureName sysname,
	@workingFolder varchar(400),
	@vssHierarchy varchar(400)

set nocount on

-- init
set @procedureName = db_name() + '.' 
			+ user_name(objectproperty(@@procid,'OwnerId')) 
                	+ '.' + object_name(@@procid)

select  @vssShare = p.vssShare,
	@vssHierarchyRoot = p.vssHierarchyRoot,
	@workingFolderRoot = s.workingFolderRoot,
        @environmentName = e.environmentName,
	@serverName = s.serverName,
	@instanceName = s.instanceName,
	@vssUserName = n.vssUserName
from Admin.dbo.ProjectNode n
inner join Admin.dbo.Project p
on n.project_id = p.id
inner join Admin.dbo.Environment e
on n.environment_id = e.id
inner join Admin.dbo.SqlServer s
on n.sqlServer_id = s.Id
where p.projectName = @pProjectName
-- no function in SQL 7, go for server and instance name
-- and s.id = Admin.dbo.thisSqlServer_id() 
and s.serverName = reverse(substring(reverse(@@servername), 
				charindex('\',reverse(@@servername)) + 1, 
                        	datalength(reverse(@@servername))))
and s.instanceName = substring(@@servername, 
               	                charindex('\',@@servername) + 1, 
                                datalength(@@servername))
and e.parentEnvironment_Id is null
and n.isActive = 1

if @@rowcount < 1
	begin
		raiserror (59003, 16, 1, @procedureName, @@servername, @pProjectName)
		goto ErrorHandler
	end

-- wrong user asking
if substring(system_user,charindex('\', system_user) + 1, datalength(system_user)) <> @vssUserName
	begin
		set @vssUserName = substring(system_user,charindex('\', system_user) + 1, datalength(system_user))
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

-- validate the drive 
set @cmd = 'dir ' + substring(@workingFolderRoot,1,2) 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if @returnStatus <> 0
	begin
		raiserror(59004, 16, 1, @procedureName, @workingFolderRoot)
		goto ErrorHandler
	end

-- start with environment level containers 
set @workingFolder = @workingFolderRoot
                        + @pProjectName + '\'
                        + @environmentName + '\' 

-- conversions that must be applied to multiple servers in an environment
set @cmd = 'dir ' + @workingFolder + 'Conversions\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'Conversions\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

-- all database development related documention for the project
set @cmd = 'dir ' + @workingFolder + 'Documentation\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'Documentation\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

-- hot fixes that must be applied to multiple servers in an environment
set @cmd = 'dir ' + @workingFolder + 'HotFixes\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'HotFixes\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

-- next create server/instance level containers
set @workingFolder = @workingFolder
                        + @serverName + '\'
                        + @instanceName + '\'

set @cmd = 'dir ' + @workingFolder + 'BackupDevices\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'BackupDevices\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

-- conversions that must be applied to multiple database on a server
set @cmd = 'dir ' + @workingFolder + 'Conversions\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'Conversions\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

-- hot fixes that must be applied to multiple databases on a server
set @cmd = 'dir ' + @workingFolder + 'HotFixes\' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'HotFixes\' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

set @cmd = 'dir ' + @workingFolder + 'JobServer\Alerts' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'JobServer\Alerts' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

set @cmd = 'dir ' + @workingFolder + 'JobServer\Jobs' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'JobServer\Jobs' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

set @cmd = 'dir ' + @workingFolder + 'JobServer\Operators' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'JobServer\Operators' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end

set @cmd = 'dir ' + @workingFolder + 'Logins' 
exec @returnStatus = master..xp_cmdshell @cmd, no_output
if (@returnStatus in (1,2))
	begin
		set @cmd = 'mkdir ' + @workingFolder + 'Logins' 
		exec @returnstatus = master..xp_cmdshell @cmd, no_output 
		if @returnstatus <> 0
			goto ErrorHandler
	end


declare cur cursor 
for
select d.databaseName
from Admin.dbo.ProjectNode n
inner join admin.dbo.project p
on n.project_id = p.id
inner join Admin.dbo.Environment e
on n.environment_id = e.id
inner join Admin.dbo.SqlServer s
on n.sqlServer_id = s.Id
inner join Admin.dbo.SqlDatabase d
on n.sqlDatabase_id = d.Id
where p.projectName = @pProjectName
and d.databaseName like (@pDatabaseName)
and n.isActive = 1

open cur
fetch next from cur into @databaseName
while @@fetch_status = 0
        begin
		-- database level containers 
		set @workingFolder = @workingFolder
	                   + @databaseName + '\'

		set @cmd = 'dir ' + @workingFolder + 'Conversions\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Conversions\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'DatabaseRoles\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'DatabaseRoles\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

  		set @cmd = 'dir ' + @workingFolder + 'Defaults\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Defaults\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end


		set @cmd = 'dir ' + @workingFolder + 'HotFixes\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'HotFixes\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Rules\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Rules\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'StoredProcedures\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'StoredProcedures\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\Checks\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\Checks\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\Defaults\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\Defaults\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\ForeignKeys\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
  			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\ForeignKeys\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\Indexes\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\Indexes\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\PrimaryKeys\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\PrimaryKeys\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\UniqueKeys\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\UniqueKeys\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tables\Triggers\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Tables\Triggers\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Tools\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
		    		set @cmd = 'mkdir ' + @workingFolder + 'Tools\'  
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end            

		set @cmd = 'dir ' + @workingFolder + 'UserDefinedDatatypes\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'UserDefinedDatatypes\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'UserDefinedFunctions\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'UserDefinedFunctions\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Users\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Users\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		set @cmd = 'dir ' + @workingFolder + 'Views\' 
		exec @returnStatus = master..xp_cmdshell @cmd, no_output
		if (@returnStatus in (1,2))
			begin
				set @cmd = 'mkdir ' + @workingFolder + 'Views\' 
				exec @returnstatus = master..xp_cmdshell @cmd, no_output 
				if @returnstatus <> 0
					goto ErrorHandler
			end

		fetch next from cur into @databaseName
	end

deallocate cur

set @cmd = 'tree ' 
	 + @workingFolderRoot
	 + @pProjectName
exec master..xp_cmdshell @cmd

-- audit
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' ' + @pProjectName + ' complete successfully'

return

ErrorHandler:
if (@Cmd is not null)
	begin
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pProjectName + ' failed with returnstatus ' 
			+ cast(@returnstatus as varchar(10)) + ' at: 
			' + @cmd
		raiserror(59001,16,1,@procedureName)
	end
else
	raiserror(59000,16,1,@procedureName)
	

if (cursor_status('local','cur') >= 0)
	deallocate cur

return -1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO