USE [HL_MONF_LOAD]
GO

/****** Object:  StoredProcedure [dbo].[LoadExamFlat]    Script Date: 01/20/2014 00:02:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[LoadExamFlatIncremental] @fullLoad int, @fileId int, @singleanumber nvarchar(50) = null as
Begin

-- you need to set up the practice names in the provider
Update Provider
	set PracticeName = mdp.MedicalGroup
	from 
	MDNamePractice mdp where Provider.LASTNAME = mdp.LastName and Provider.FirstName = mdp.FirstName



SET NOCOUNT ON;

declare @curdt datetime
set @curdt = GETDATE()

declare @today datetime
set @today = cast(cast(@curdt as date) as datetime)

declare @startedOn datetime
declare @listenerGuid nvarchar (100)
declare @status varchar(20) = 'SUCCESS' 
declare @loadTableName varchar(30)
declare @accColumnName varchar(30)
declare @sql varchar(500)

exec LogMessage @fileId,  'SP_UPDATE_DW','Exam Flat Load Begin',@status,@startedOn
set @startedOn = GETDATE()		

-- this is just for safety check, so people don't accidently run it
if @fullLoad <> 1 and @fullLoad <> 747 
Begin
	exec LogMessage @fileId,  'SP_UPDATE_DW','Wrong Parameters so procedure not executed',@status,@startedOn
	return
End

if @fullLoad = 747
Begin
	set @startedOn = GETDATE()
	truncate table HL_MONF_DATA.dbo.ExamFlat
	exec LogMessage @fileId, 'SP_UPDATE_DW','Tables truncated','SUCCESS',@startedOn
End

set @startedOn = GETDATE()
Select @listenerGuid = L.Guid,
	@loadTableName  = L.LoadTableName,
	@accColumnName  = L.ProcedureIdColumnName
from 
	DataLoadListener L
	inner join DataLoadProcess P on L.Id = P.ListenerId
	inner join DataLoadFile F on P.Id = F.ProcessId
WHERE 
	F.Id = @fileId
	
--select distinct AccessionNumber into #DailyDataLoadAccNo from dbo.fnDailyDataLoad(@listenerGUID) where AccessionNumber is not null
if(@fullLoad = 747)
	set @sql = 'Select distinct AccessionNumber from FillerOrder'
else
	set @sql = 'Select distinct ' + @accColumnName + ' from ' + @loadTableName + ' where FileId = ' + cast(@fileId as varchar)

Create Table #DailyDataLoadAccNo(AccessionNumber nvarchar(100))

if ((@fileId = 0) and (@singleanumber <> ''))
BEGIN
insert into #DailyDataLoadAccNo select @singleanumber
END
ELSE
BEGIN
insert into #DailyDataLoadAccNo
exec(@sql)
END


if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' 
exec LogMessage @fileId,  'SP_UPDATE_DW','Data from DailyDataLoad Loaded into Temp',@status,@startedOn
set @startedOn = GETDATE()










	


Merge ExamFlat EF
Using 
(
	Select 
	FillerOrderKey,ClinicalKey,ModalityKey,FinancialChargeKey,PatientKey,PlacerOrderNumber,
	AccessionNumber,PatientMRN,Priority,PreProcedureDuration,PostProcedureDuration,TechnicalRVU,ProfessionalRVU,TotalRVU,
	TechnicalCharge,ProfessionalCharge,
	ModalityType,ModalityName,ModalityLocation,Age,Gender,
	MaritalStatus,Race,Language,Nationality,NumberOfChildren, BodyPart,ExamCode,CPTCode,
	PatientType,
	OrderSpecialty,OrderLocation,
	InsuranceProviderName,
	ZipCode, ExamCodeDescription, ICD9Code, 
	ChargeCode, 
	PatientClassCode, 
	OrderLocationCode,ChargeToPractice,ChargeType, ServicingFacility, AccountNumber,
	OrderPhysicianName, ReportingPhysicianName, TechnologistName, ResidentFellowName,
	OrderPhysicianPracticeName, ProcedureClass, ProcedureName, InsurancePlanName,
	OrderToFinalizeDuration
	from vwExam
	where AccessionNumber in (select AccessionNumber from #DailyDataLoadAccNo)
) Source
on EF.AccessionNumber = Source.AccessionNumber
when MATCHED THEN
UPDATE SET
	FillerOrderKey, 
	ClinicalKey, 
	ModalityKey, 
	FinancialChargeKey, 
	PatientKey, 
--	PatientCount, 
--	ExamCount, 
	PlacerOrderNumber,
	AccessionNumber, 
	PatientMRN, 
	Priority, 
	PreProcedureDuration, 
	PostProcedureDuration, 
	TechnicalRVU, 
	ProfessionalRVU, 
	TotalRVU,
--	Cost, 
	TechnicalCharge, 
	ProfessionalCharge, 
--	Collected, 
--	AETitle, 
	ModalityType, 
	ModalityName, 
	ModalityLocation, 
	Age, 
	Gender,
	MaritalStatus, 
	Race, 
	Language, 
	Nationality,
	NumberOfChildren,
	BodyPart,
	ExamCode,
	cast(CPTCode as nvarchar(30)) as CPTCode,
--	LOINCCd,
--	ExamInterpretedCount,
--	PeerReviewCode,
--	DiscrepancyCode, 
--	NormalFinding, 
--	CriticalFindingCode, 
	PatientType, 
--	OrderSpecialty, 
--	OrderLocation,
--	null as InsuranceProviderName, 
--	null as RadiationDose, 
--	null as Contrast,
--	null as StudyId, 
--	null as ImagesCount, 
--	null as SeriesCount,
--	null as StudyCount,
--	FeePerStudy, 
--	DLP, CTDIvol, 
	Cast(ZipCode As varchar(5)) AS ZipCode, 
--	BilledCharge, 
	ExamCodeDescription, 
	cast (ICD9Code as nvarchar(30)), 
	ChargeCode,
	PatientClassCode, 
	cast(OrderLocationCode as varchar(20)) as OrderLocationCode, 
--	cast(ChargeToPractice as varchar(20)) as ChargeToPractice,
--	null as ChargeType, 
	cast (ServicingFacility as varchar(100)) as ServicingFacility, 
	AccountNumber, 
	ReferringMD, 
	AttendingMD, 
	Technologist, 
	ResidentFellowName,
	ReferringMDPracticename, 
	ProcedureClass, 
	ProcedureName, 
	InsurancePlanName,
	OrderToFinalizeDuration
from
	vwExam

Update ExamFlat
set 
	--PreProcedureDuration = DT.PreProcedureDuration,
	--PostProcedureDuration = DT.PostProcedureDuration,
	OrderDate = SchDT.CalendarDateAlternateKey,
	OrderDateTimeYear = SchDT.Year,
	OrderDateTimeQuarter =  SchDT.Quarter,
	OrderDateTimeMonthName = SchDT.MonthName,
	OrderDateTimeMonthNumber = SchDT.MonthNumber,
	OrderDateTimeDay = SchDT.Day,
	OrderDateTimeWeekNumber = SchDT.WeekNumber,
	OrderDateTimeDayOfWeek = SchDT.DayOfWeek,
	OrderTime = CAST(DT.ScheduledDateTime as Time),
	OrderDateTime = DT.ScheduledDateTime,

	ScheduleDate = SchDT.CalendarDateAlternateKey,
	ScheduleDateTimeYear = SchDT.Year,
	ScheduleDateTimeQuarter =  SchDT.Quarter,
	ScheduleDateTimeMonthName = SchDT.MonthName,
	ScheduleDateTimeMonthNumber = SchDT.MonthNumber,
	ScheduleDateTimeDay = SchDT.Day,
	ScheduleDateTimeWeekNumber = SchDT.WeekNumber,
	ScheduleDateTimeDayOfWeek = SchDT.DayOfWeek,
	ScheduleTime = CAST(DT.ScheduledDateTime as Time),
	ScheduleDateTime = DT.ScheduledDateTime,
	
	ExamReadDate = ErDT.CalendarDateAlternateKey,
	ExamReadDateTimeYear = ErDT.Year,
	ExamReadDateTimeQuarter =  ErDT.Quarter,
	ExamReadDateTimeMonthName = ErDT.MonthName,
	ExamReadDateTimeMonthNumber = ErDT.MonthNumber,
	ExamReadDateTimeDay = ErDT.Day,
	ExamReadDateTimeWeekNumber = ErDT.WeekNumber,
	ExamReadDateTimeDayOfWeek = ErDT.DayOfWeek,
	ExamReadTime = CAST(DT.ExamReadDateTime as Time),
	ExamReadDateTime = DT.ExamReadDateTime,	
	
	ExamEndDate = EcDT.CalendarDateAlternateKey,
	ExamEndDateTimeYear = EcDT.Year,
	ExamEndDateTimeQuarter =  EcDT.Quarter,
	ExamEndDateTimeMonthName = EcDT.MonthName,
	ExamEndDateTimeMonthNumber = EcDT.MonthNumber,
	ExamEndDateTimeDay = EcDT.Day,
	ExamEndDateTimeWeekNumber = EcDT.WeekNumber,
	ExamEndDateTimeDayOfWeek = EcDT.DayOfWeek,
	ExamEndTime = CAST(DT.ExamEndDateTime as Time),
	ExamEndDateTime = DT.ExamEndDateTime,

	DateofService = DT.DateOfServiceDateTime,
	DateofServiceYear = DsDT.Year,
	DateofServiceQuarter =  DsDT.Quarter,
	DateofServiceMonthName = DsDT.MonthName,
	DateofServiceMonthNumber = DsDT.MonthNumber,
	DateofServiceDay = DsDT.Day,
	DateofServiceWeekNumber = DsDT.WeekNumber,
	DateofServiceDayOfWeek = DsDT.DayOfWeek,
	DateofServiceTime = CAST(DT.ExamEndDateTime as Time),


	ProcedureCancelledDateTime = DT.ProcedureCancelledDateTime,
	ProcedureCancelledYear = DcxDT.Year,
	ProcedureCancelledQuarter =  DcxDT.Quarter,
	ProcedureCancelledMonthName = DcxDT.MonthName,
	ProcedureCancelledMonthNumber = DcxDT.MonthNumber,
	ProcedureCancelledDay = DcxDT.Day,
	ProcedureCancelledWeekNumber = DcxDT.WeekNumber,
	ProcedureCancelledDayOfWeek = DcxDT.DayOfWeek,
	ProcedureCancelledTime = CAST(DT.ExamEndDateTime as Time),

	FinalizeDate = FiDT.CalendarDateAlternateKey,
	FinalizeDateTimeYear = FiDT.Year,
	FinalizeDateTimeQuarter =  FiDT.Quarter,
	FinalizeDateTimeMonthName = FiDT.MonthName,
	FinalizeDateTimeMonthNumber = FiDT.MonthNumber,
	FinalizeDateTimeDay = FiDT.Day,
	FinalizeDateTimeWeekNumber = FiDT.WeekNumber,
	FinalizeDateTimeDayOfWeek = FiDT.DayOfWeek,
	FinalizeTime = CAST(DT.FinalizedDateTime as Time),
	FinalizeDateTime = DT.FinalizedDateTime,
	
	ChargesBilledDate = CbDT.CalendarDateAlternateKey,
	ChargesBilledDateTimeYear = CbDT.Year,
	ChargesBilledDateTimeQuarter =  CbDT.Quarter,
	ChargesBilledDateTimeMonthName = CbDT.MonthName,
	ChargesBilledDateTimeMonthNumber = CbDT.MonthNumber,
	ChargesBilledDateTimeDay = CbDT.Day,
	ChargesBilledDateTimeWeekNumber = CbDT.WeekNumber,
	ChargesBilledDateTimeDayOfWeek = CbDT.DayOfWeek,
	ChargesBilledDateTime = DT.ChargesBilledDateTime,

	/*OrderPhysicianKey = PrOr.ProviderKey,
	OrderPhysicianName = PrOr.OrderPhysician,	
	OrderPhysicianPracticeName = PrOr.OrderPhysicianPracticeName,*/
	SigningRadiologistName = PrFI.SigningPhysician,
	SigningRadiologistPracticeName = PrFI.SigningPhysicianPracticeName
	
from 
--Select ef.FillerOrderkey, PrOr.ProviderKey,PrOr.OrderPhysician, PrFI.SigningPhysician,* from
ExamFlat EF
left join 
(
	Select FillerOrderKey, OrderedDateTime, ScheduledDateTime, ProcedureCancelledDateTime, DateOfServiceDateTime, ExamStartDateTime, ExamEndDateTime, ExamReadDateTime, FinalizedDateTime, ChargesBilledDateTime,
	PreProcedureDuration = 
			case when ScheduledDateTime is null or ExamEndDateTime is null
		then NULL else datediff(ss,ScheduledDateTime,ExamEndDateTime) end,
	PostProcedureDuration = 
			case when FinalizedDateTime is null or ExamEndDateTime is null
		then 0 else datediff(ss,ExamEndDateTime,FinalizedDateTime) end
	from
		(
			Select 
				FO.FillerOrderKey, OrderedDateTime,	ScheduledDateTime, ProcedureCancelledDateTime, DateOfServiceDateTime, ExamStartDateTime, ExamEndDateTime, ExamReadDateTime, FinalizedDateTime, ChargesBilledDateTime
			 from
			 FillerOrder FO
			 left join 
			(Select FillerOrderKey, CalendarDateTime as OrderedDateTime from ExamWorkflow EWF				
				where ExamState = 'OR')Dor  ON FO.FillerOrderKey = Dor.FillerOrderKey
			LEFT JOIN 
			(Select FillerOrderKey, CalendarDateTime as ScheduledDateTime from ExamWorkflow EWF				
				where ExamState = 'SC')SC  ON FO.FillerOrderKey = SC.FillerOrderKey
			LEFT JOIN 
			(Select FillerOrderKey, min(CalendarDateTime) as ProcedureCancelledDateTime from ExamWorkflow 
				where ExamState = 'CX' group by FillerOrderKey)Dcx ON FO.FillerOrderKey = Dcx.FillerOrderKey 
			LEFT JOIN 
			(Select FillerOrderKey, max(CalendarDateTime) as DateOfServiceDateTime from ExamWorkflow 
				where ExamState in ('EB', 'EC') group by FillerOrderKey)DS ON FO.FillerOrderKey = DS.FillerOrderKey 
			LEFT JOIN 
			(Select FillerOrderKey, min(CalendarDateTime) as ExamStartDateTime from ExamWorkflow 
				where ExamState = 'EB' group by FillerOrderKey)ES ON FO.FillerOrderKey = ES.FillerOrderKey 
			LEFT JOIN 
			(Select FillerOrderKey, max(CalendarDateTime) as ExamEndDateTime from ExamWorkflow 
				where ExamState = 'EC' group by FillerOrderKey)EC ON FO.FillerOrderKey = EC.FillerOrderKey 
			LEFT JOIN 
			(Select FillerOrderKey, CalendarDateTime as ExamReadDateTime from ExamWorkflow EWF				
				where ExamState = 'SD')SD  ON FO.FillerOrderKey = SD.FillerOrderKey				
			LEFT JOIN 
			(Select FillerOrderKey, max(CalendarDateTime) as FinalizedDateTime from ExamWorkflow 
				where ExamState = 'FI' group by FillerOrderKey)FI ON FO.FillerOrderKey = FI.FillerOrderKey
			LEFT JOIN 
			(Select FillerOrderKey, CalendarDateTime as ChargesBilledDateTime from ExamWorkflow 
				where ExamState = 'CB')CB ON FO.FillerOrderKey = CB.FillerOrderKey				
		)T		
)DT ON EF.FillerOrderKey = DT.FillerOrderKey
LEFT join 
(
	Select FillerOrderKey, Pr.ProviderKey, FullName as OrderPhysician, PracticeName as OrderPhysicianPracticeName from ExamWorkflow EWF				
	INNER join Provider PR on EWF.ProviderKey = PR.ProviderKey
	where ExamState = 'OR'

)PrOr ON EF.FillerOrderKey = PrOr.FillerOrderKey
LEFT join 
(
	Select FillerOrderKey, Pr.ProviderKey, FullName as SigningPhysician, PracticeName as SigningPhysicianPracticeName from ExamWorkflow EWF				
	INNER join Provider PR on EWF.ProviderKey = PR.ProviderKey
	where ExamState = 'FI'
)PrFI ON EF.FillerOrderKey = PrFI.FillerOrderKey
LEFT join CalendarDate SchDT on cast(DT.ScheduledDateTime as date) = SchDT.CalendarDateAlternateKey	
LEFT join CalendarDate DcxDT on cast(DT.ProcedureCancelledDateTime as date) = DcxDT.CalendarDateAlternateKey	
LEFT join CalendarDate DsDT on cast(DT.DateOfServiceDateTime as date) = DsDT.CalendarDateAlternateKey	
LEFT join CalendarDate EsDT on cast(DT.ExamStartDateTime as date) = EsDT.CalendarDateAlternateKey	
LEFT join CalendarDate EcDT on cast(DT.ExamEndDateTime as date) = EcDT.CalendarDateAlternateKey	
LEFT join CalendarDate ErDT on cast(DT.ExamReadDateTime as date) = ErDT.CalendarDateAlternateKey	
LEFT join CalendarDate FiDT on cast(DT.FinalizedDateTime as date) = FiDT.CalendarDateAlternateKey	
LEFT join CalendarDate CbDT on cast(DT.FinalizedDateTime as date) = CbDT.CalendarDateAlternateKey	
where AccessionNumber in (select AccessionNumber from #DailyDataLoadAccNo)

------------------------------------------------------
-- Patient Drill Thru
------------------------------------------------------
exec [LoadPatientDrillThru]

End
GO