/*
Type:		Stored Procedure
Name:		dbo.getDirectoryFileList
Author:		Boyan Kostadinov
Created:	03.17.2008
Dependencies:	master.dbo.xp_cmdshell
Usage:		exec dbo.getDirectoryFileList 'c:\temp', null
		exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'
Parameters:	@directoryPath varchar(255)
		- The path of the local or network directory

		@fileExtensionFilter varchar(10) - Optional
		- The file extension to filter the file list by
Returns:	A list of files found on the file system
*/
create procedure dbo.getDirectoryFileList
	@directoryPath varchar(255),
	@fileExtensionFilter varchar(10) = null
as

set nocount on

-- Declare and initialize local variables
declare @dosCommand varchar(5000)
set @dosCommand = ''

-- If the file extension fileter was empty, set it to all files
if @fileExtensionFilter is null or ltrim(rtrim(@fileExtensionFilter)) = ''
	set @fileExtensionFilter = '*.*'

-- If the directory path does not have an ending '\', append one
if substring(@directoryPath, len(@directoryPath), 1) <> '\'
	set @directoryPath = @directoryPath + '\'

-- Build the dos command to get a list of files
select @dosCommand =
	'insert into #tempFileList(fileListRow) ' +
	'exec master.dbo.xp_cmdshell ''dir ' + @directoryPath + + @fileExtensionFilter + ''''

-- Create a temporary table to store the file list
create table #tempFileList (
	fileListRow varchar(1000) null
)

-- Create the #fileList temporary table to store the file list
create table #fileList (
	fileID int primary key identity(1,1) not null,
	[fileName] varchar(255) not null,
	lastModifiedOn datetime not null,
	fileSize bigint not null,
)

exec(@dosCommand)

-- 8 - Delete unneeded data from the #OriginalFileList
delete from #tempFileList
where fileListRow is null

delete from #tempFileList
where fileListRow like '%Volume%'

delete from #tempFileList
where fileListRow like '%Directory%'

delete from #tempFileList
where fileListRow like '%<DIR>%'

delete from #tempFileList
where fileListRow like '%bytes%'

if not exists (select * from #tempFileList where fileListRow like '%access is denied%')
begin
	-- Populate the #fileList table with the final data
	insert into #fileList(lastModifiedOn, fileSize, [fileName])
	select	ltrim(substring(fileListRow, 1, 10))
		+
		' '
		+
		rtrim(ltrim(substring(fileListRow, 11, 15)))
		+
		'm'
		as 'lastModifiedOn',
		replace(ltrim(substring(fileListRow, 21, 18)), ',', '') as 'fileSize',
		ltrim(substring(fileListRow, 40, 1000)) as 'fileName'
	from	#tempFileList

	select * from #fileList
end
else
	select fileListRow as errorMessage from [#tempFileList] as e

-- Drop the temporary tables
drop table #tempFileList
drop table #fileList

set nocount off
go