USE [HL_VA_LOAD]
GO
/****** Object:  StoredProcedure [dbo].[LoadDataDaily]    Script Date: 09/23/2013 17:11:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[LoadDataDaily] @listenerId int, @fileId int, @debug int = 0
as
Begin

SET NOCOUNT ON

declare @columnName varchar(40)
declare @fieldName varchar(200)
declare @sqlDatatype varchar(20)
declare @fieldLength int
declare @rowId int
declare @accNumber varchar(200)
declare @logMsg varchar(100)

declare @insertSql varchar(8000)
declare @selectSql varchar(4000)
declare @createTableSql varchar(8000)
declare @fieldsSql varchar(4000)
declare @startedOn datetime = getdate()

declare @insertTableName varchar(100)

set @selectSql = ''
set @insertSql = ''
set @fieldsSql = ''
set @createTableSql = ''

DECLARE curFields CURSOR FOR
	Select columnName, FieldName, SqlDataType, SqlFieldLength from DataLoadField where Available = 'Y' and ListenerId = @listenerId order by FieldOrder

OPEN curFields
FETCH NEXT FROM curFields INTO @columnName, @fieldName, @sqlDatatype, @fieldLength;

WHILE @@FETCH_STATUS = 0
BEGIN
	if (@selectSql <> '') 
	Begin
		set @selectSql = @selectSql + ','
		set @createTableSql = @createTableSql + ','
		set @fieldsSql = @fieldsSql + ','
	End
	
	set @fieldsSql = @fieldsSql + @fieldName
	set @selectSql = @selectSql + @columnName + ' as ' + @fieldName 
	set @createTableSql = @createTableSql + @fieldName + ' ' + @sqlDatatype
	if(@fieldLength <> 0)
		set @createTableSql = @createTableSql + '(' + CAST(@fieldLength as varchar(10)) + ')'
	
	FETCH NEXT FROM curFields INTO @columnName, @fieldName, @sqlDatatype, @fieldLength;

END

CLOSE curFields;
DEALLOCATE curFields;

declare @listenerGuid nvarchar (100)

Select @listenerGuid = Guid, @insertTableName = LoadTableName from DataLoadListener where Id = @listenerId 

declare @loadTableName varchar(100)
set @loadTableName = 'DataLoadFormatted'

set @selectSql = 'Select RowId, FileId,' + @selectSql + ' from ' + @loadTableName +
' where FileId = ' + cast(@fileId as varchar)

if @listenerGuid = '5b3c8103-1f33-42b2-b4f2-deef58788afe'
Begin
	-- SciImage
	-- ServerDatetime as TimeToServer, StudyDateTime as StudyRead, DeliveredDateTime as SignedOn	
	-- TimeToServer as ExamEndDateTime, StudyRead as ExamReadDateTime, SignedOn as FinalizeDateTime,

	-- Load it with the earliest time
	set @insertSql = 
	'With cteDataLoadFormatted AS (' + @selectSql + ') insert into ' + @insertTableName + 
	' Select DF.RowId, DF.FileId, DTF.ServerDateTime,DTF.DeliveredDateTime,OrderStatus,ExamOriginatingSite,OrderNumber,DF.AccessionNumber,ModalityType,DTF.StudyDateTime,StudyDesc,CPTCode,BodyPart,Priority,ReasonForExam,MRN,NoOfImages,ReferringPhysician, ReadingPhysician,Remarks,FollowUp,StudyUID,Notes
	from 
		cteDataLoadFormatted DF
	inner join 
	(
		Select AccessionNumber, min(RowId) as RowId, min(FileId) as FileId, 
		min(ServerDateTime) as ServerDateTime, min(DeliveredDateTime) as DeliveredDateTime, 
		min(StudyDateTime) as StudyDatetime
		from
		(
			Select 
				RowId, FileId, AccessionNumber, ServerDateTime, StudyDateTime, DeliveredDateTime
			From 
				cteDataLoadFormatted
			union
			Select 
				NULL as RowId, NULL as FileId, AccessionNumber, ExamEndDateTime,  ExamReadDateTime, FinalizeDateTime   
			from 
				Exam 
			where AccessionNumber in (Select accessionNumber from cteDataLoadFormatted)
		)T
		group by AccessionNumber
	)DTF
	on DF.RowId = DTF.RowId'

End
else if @listenerGuid = '5ce557d2-c79e-441a-8cf2-b5c07d04a8b6'
Begin
-- CFind

set @insertSql = 
	'With cteDataLoadFormatted AS (' + @selectSql + ') insert into ' + @insertTableName + 
	' Select DF.RowId, DF.FileId, cast(DTF.StudyDateTime as Date), cast(DTF.StudyDateTime as Time), DF.AccessionNumber,ModalityType, ReferringPhysician, StudyDesc,PatientName, MRN, PatientBirthDateTime, PatientSex, StudyUID,CPTCode, ImageCount
	from 
		cteDataLoadFormatted DF
	inner join 
	(
		Select AccessionNumber, min(RowId) as RowId, min(FileId) as FileId, min(StudyDateTime) as StudyDatetime
		from
		(
			Select 
				RowId, FileId, AccessionNumber, dbo.fnGetDateTime(StudyDate,StudyTime) as StudyDateTime
			From 
				cteDataLoadFormatted
			union
			Select 
				NULL as RowId, NULL as FileId, AccessionNumber, ExamReadDateTime
			from 
				Exam 
			where AccessionNumber in (Select accessionNumber from cteDataLoadFormatted)
		)T
		group by AccessionNumber
	)DTF
	on DF.RowId = DTF.RowId'
	
End
else 
Begin	
	set @insertSql = 'insert into ' + @insertTableName + ' ' + @selectSql 
End

declare @dropTableSql varchar(400)

set @dropTableSql = 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + @insertTableName + ''') AND type in (N''U'')) DROP TABLE ' + @insertTableName

set @createTableSql = 'Create Table ' + @insertTableName + '( Id int, FileId int,' + @createTableSql + ');'

if @debug  = 1
Begin
	print 'Drop   : ' + @dropTableSql;
	print 'Create : ' + @createTableSql;
	print 'Select : ' + @selectSql;
	print 'Insert : ' + @insertSql;
	print 'Field  : ' + @fieldsSql;
End
	
exec(@dropTableSql); --print 'Drop done'; 
exec(@createTableSql); --print 'create done';
exec(@insertSql); --print 'insert done';

exec LogMessage @fileId,  'SP_UPDATE_DW','Load Data Daily table updated','SUCCESS',@startedOn

SET NOCOUNT OFF

End