home
-- Datawarehouse builder - polling for files
Author Nigel Rivett
alter proc [dbo].[s_FileProcess]
@Entity varchar(50) ,
@Action varchar(50) , -- InsertFile, GetFilesList, GetFilesToDelete
@Status varchar(100) ,
@FileName varchar(1000) = null ,
@Data varchar(max) = null
as
/*
begin tran
exec s_FileProcess @Entity = 'TestControl', @Action = 'InsertFile', @Status = 'FileToDownload', @FileName='Test1.done'
exec s_FileProcess @Entity = 'TestControl', @Action = 'InsertFile', @Status = 'FileToDownload', @FileName='Test2.done'
exec s_FileProcess @Entity = 'TestControl', @Action = 'GetFilesList', @Status = 'FileToDownload'
exec s_FileProcess @Entity = 'TestControl', @Action = 'InsertFile', @Status = 'FileToDownloaded', @FileName='Test1.tar.gz'
exec s_FileProcess @Entity = 'TestControl', @Action = 'InsertFile', @Status = 'FileToDownloaded', @FileName='Test2.tar.gz'
exec s_FileProcess @Entity = 'TestControl', @Action = 'GetFilesToDelete', @Status = 'FileToDownloaded'
rollback tran
begin tran
exec s_FileProcess @Entity = 'Test', @Action = 'InsertFile', @Status = 'FileToDownload', @FileName='Test1.fin'
exec s_FileProcess @Entity = 'Test', @Action = 'InsertFile', @Status = 'FileToDownload', @FileName='Test2.fin'
exec s_FileProcess @Entity = 'Test', @Action = 'GetFilesList', @Status = 'FileToDownload'
exec s_FileProcess @Entity = 'Test', @Action = 'InsertFile', @Status = 'FileToDownloaded', @FileName='Test1.fin'
exec s_FileProcess @Entity = 'Test', @Action = 'InsertFile', @Status = 'FileToDownloaded', @FileName='Test2.fin'
exec s_FileProcess @Entity = 'Test', @Action = 'GetFilesToDelete', @Status = 'FileToDownloaded'
rollback tran
select top 100 * From fileprocess order by fileprocess_id desc
exec s_FileProcess @Entity = 'DClick_NetworkImpression_3995', @Action = 'GetFilesList', @Status = 'FileToDownload'
*/
if @Entity like 'DUV%'
insert Trace (Entity, Key1, data1, data2)
select 's_FileProcess', @Action, @Entity + ', ' + coalesce(@FileName, ''), @Status
declare @CurrentStatus varchar(100)
declare @DataFileName varchar(1000)
declare @ExtractType varchar(20)
declare @ControlFileExt varchar(20)
declare @DataFileExt varchar(20)
declare @ProcessType table (ProcessName varchar(50), ControlFileExt varchar(20), DataFileExt varchar(20), ExtractType varchar(20))
insert @ProcessType select 'TestControl1', '.done', '.tar.gz', 'ControlFile' -- key on control file - file to load has different name
insert @ProcessType select 'TestControl2', '', '', 'Normal' -- file to load as in parameter
insert @ProcessType select 'TestControl3', '', '', 'NotLast' -- do not load last file
insert @ProcessType select 'TestControl4', '', '', 'After2Hours' -- give 2 hours for upload to complete
insert @ProcessType select 'TestControl4', '', '', 'NotLastDay' -- do not oad last days files
insert @ProcessType select 'Classic', '', '', 'Normal'
insert @ProcessType select 'NewWorld', '', '', 'Normal'
select @ExtractType = ExtractType ,
@ControlFileExt = ControlFileExt ,
@DataFileExt = DataFileExt
from @ProcessType
where ProcessName = @Entity
if @ExtractType = 'ControlFile'
select @DataFileName = REPLACE(@FileName, @ControlFileExt, @DataFileExt)
if @DataFileName is null
select @DataFileName = @FileName
if @Action = 'InsertFile'
begin
select @CurrentStatus = Status
from FileProcess
where Entity = @Entity
and FileName = @DataFileName
if @CurrentStatus is null
begin
insert FileProcess
(
Entity ,
FileName ,
Status ,
Data
)
select
@Entity ,
FileName = @DataFileName ,
@Status ,
@Data
from (select Entity = @Entity) a
end
else if @CurrentStatus <> 'Complete' -- complete - no going back
and @Status <> 'FileToDownload' -- cannot update to FileToDownload
begin
update FileProcess
set Status = @Status ,
Data = @Data ,
z_updated = GETDATE()
where Entity = @Entity
and FileName = @DataFileName
end
end
else if @Action = 'GetFilesList'
begin
if @ExtractType = 'After2Hours'
begin
select FileName
from FileProcess
where Entity = @Entity
and Status = @Status
and z_inserted < dateadd(hh,-2,getdate())
end
if @ExtractType = 'NotLast'
begin
select FileName
from FileProcess
where Entity = @Entity
and Status = @Status
and FileName <> ( select max(FileName)
from FileProcess
where Entity = @Entity
and Status = @Status
)
end
if @ExtractType = 'NotLastDay' -- DClick_NetworkImpression_3995 only at the moment
begin
select FileName
from FileProcess
where Entity = @Entity
and Status = @Status
and right(FileName, 17) <> ( select top 1 right(FileName, 17)
from FileProcess
where Entity = @Entity
and Status = @Status
order by FileProcess_id desc
--order by convert(datetime,left(right(FileName, 17),10),103) desc
)
end
if @ExtractType not in ('NotLast', 'NotLastDay','After2Hours')
begin
select FileName
from FileProcess
where Entity = @Entity
and Status = @Status
end
end
else if @Action = 'GetFilesToDelete'
begin
select FileName = case when pt.Type = 'C'
then REPLACE(fp.FileName, pt.DataFileExt, pt.ControlFileExt)
else fp.FileName
end
from FileProcess fp
left join ( select ProcessName, Type = 'C', ControlFileExt, DataFileExt from @ProcessType where ProcessName = @Entity
union all
select ProcessName, Type = 'D', ControlFileExt, DataFileExt from @ProcessType where ProcessName = @Entity
) pt
on fp.Entity = pt.ProcessName
where fp.Entity = @Entity
and fp.Status = @Status
end
GO
home