IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrdGetVisit]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OrdGetVisit]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- OrdGetVisit is a stored procedure used by the orders_transfer trigger.
-- OrdGetVisit returns an integer that is a MIKPatientVisitId value.
-- If an existing entry cannot be found in MIKPatientVisit table, then
-- the returned value may be null.
/*
The OrdGetVisit stored procedure returns the following columns.
	MIKPatientVisitId int,
	OrderVisitId int,
	OrderApptId int

It is possible for multiple orders to be associated with different visits for a given document.
A non-null, positive value is returned for OrderVisitId only if all
of the following conditions are met.
* The order document (given by the input @OrderSDID parameter) exists.
* The order document is linked to an appointment.
* The appointment linked to the order document is linked to an existing visit.
* The supervising doctor for the existing visit is the same as the authorizing provider
  (given by the input @OrderAuthByDoctorId parameter) and the doctor for the existing visit
  is the same as the order document provider (given by the input @OrderDoctorId parameter)
  for the order being completed.

*/

CREATE PROC [dbo].[OrdGetVisit] (
	@OrderLinkId numeric(19,0), -- SPR 13272 Used to determine order sign date time
	@OrderSDID numeric(19,0),
	@OrderDocumentDate numeric(19,0),
	@OrderDate datetime,
	@OrderPatientProfileId int,
	@OrderDoctorId int, -- from order document
	@OrderAuthByDoctorId int,
	@OrderFacilityId int -- derived from order locofservice
)
WITH EXECUTE AS CALLER
AS
BEGIN
	SET NOCOUNT ON

	-- SPR 13272 Visit DOS will be determined and returned
	DECLARE @VisitDOS datetime	
	set @VisitDOS = NULL

	-- SPR 16172 An Approved visit has no seconds so we need to truncate it off of
	-- the Visit DOS when attempting to find an existing PatientVisit entry that has been Approved
	DECLARE @ApprvVisitDOS datetime
	set @ApprvVisitDOS = NULL

	DECLARE @OrderApptId int
	SET @OrderApptId = NULL

	DECLARE @Debug int
	SET @Debug = 0

	DECLARE @OrderVisitId int
	SET @OrderVisitId = NULL

	DECLARE @DocumentLOC numeric(19,0)	
	DECLARE @DocumentFacilityId int
	SET @DocumentLOC = (select LOCOFCARE from [DOCUMENT] where SDID = @OrderSDID)
	SET @DocumentFacilityId = (select FacilityId from LOCREG where LOCID = @DocumentLOC)

	/**
	 Execute 2 queries in an attempt to find the PatientVisitId.
	 These queries are similar to stored procedures used by MIK import of charges.
	 Separate queries are required because the MIK guessing procedures
	 require a PM Facility but EMR Locations will not be mapped to PM facilities.
	**/
	IF @Debug = 1
	BEGIN
		PRINT 'Start of OrdGetVisit: query Appointments and PatientVisit'
	END

	-- SPR 15991 If the order date is the same as the document date then attempt to find an appointment
	--  based on the document, else assume no appointment
	-- SPR 18402
	--  Determine if the Document facility and the Order facility differ and if the order date is the
	--  same as the document date.  If so we cannot base an appointment lookup on the document being assigned to the order.
	declare @datetime1 datetime
	declare @datetime2 datetime

	set @datetime1 = (select CONVERT(varchar,@OrderDate,101))
	set @datetime2 = (select CONVERT(varchar, dbo.Convert_ID_to_date(@OrderDocumentDate), 101))

	IF( (@OrderFacilityId = @DocumentFacilityId) and (@datetime1 = @datetime2) )
	begin
		set @OrderApptId = (select AppointmentsId from dbo.[DOCUMENT] where SDID=@OrderSDID)

		-- Check if there is an appointmentid assigned to the document
		IF(@OrderApptId is not NULL)
		begin
			-- Get the OrderVisitId assigned to the appointment
			SET @OrderVisitId = (select PatientVisitId from Appointments where AppointmentsId = @OrderApptId)
		end	
	end
	
	IF @Debug = 1
	BEGIN
		IF (@OrderApptId IS NOT NULL) AND (@OrderApptId > 0)
		BEGIN
			PRINT 'AppointmentsId ' + CONVERT(varchar, @OrderApptId)
				+ ' with VisitId = ' + CONVERT(varchar, @OrderVisitId) + ' found from order document'
		END
	END

	/** SPR 13272 - On the fly charge mapping to an existing appointment
    If a patient visit has not been found and the order is on the same date as the document, check to see if there isn't one that has not been
    associated by matches all of the following four criteria...
      1. Appointment Patient = Order Patient
      2. Appointment DoctorId = Order DoctorId
      3. Appointment StartTime = Document's Clinical Date and Time
      4. Appointment Facility = Order Facility            
   **/      

   IF( (@OrderApptId is NULL or @OrderApptId <= 0) and (@datetime1 = @datetime2) )
	begin
		SELECT @OrderApptId = a.AppointmentsId,
			@OrderVisitId = a.PatientVisitId,
			@VisitDOS = a.ApptStart
			FROM 
				Appointments a
			WHERE 
				a.OwnerId = @OrderPatientProfileId and
				a.DoctorId = @OrderDoctorId and
				a.ApptStart = dbo.Convert_ID_to_date(@OrderDocumentDate) and
				a.FacilityId = @OrderFacilityId
	end
	
	/**
	 If a patient visit has been found, then query MIKPatientVisit where PatientVisitId
	 matches the PatientVisitId value from the "Determine Visit" logic above.

	 If an apointment has been found (but a patient visit has not), then query MIKPatientVisit
	 where AppointmentsId value from the "Determine Visit" logic above.

	 In both cases, also make sure the DoctorId in MIKPatientVisit matches the passed in OrderDoctorId.

	 If exactly one row is found, then return the MIKPatientVisitId value so it can be used to update
	 the existing entry and associate a the newly completed order with it.
	**/
	DECLARE @aMIKPatientVisitId int
	SET @aMIKPatientVisitId = NULL
	IF (@OrderVisitId IS NOT NULL) AND (@OrderVisitId > 0)
	BEGIN
		IF @Debug = 1
		BEGIN
			PRINT 'Look for Order Visit Id = ' + CONVERT(varchar, @OrderVisitId) + ' in MIKPatientVisit'
		END

		SELECT @aMIKPatientVisitId = mpv.MIKPatientVisitId,
			@VisitDOS = mpv.DateOfService	-- SPR 13272 Set the VisitDOS
			FROM MIKPatientVisit mpv
			WHERE mpv.PatientVisitId = @OrderVisitId
			AND mpv.DoctorId = @OrderDoctorId
			AND mpv.SupervisingDoctorId = @OrderAuthByDoctorId
			AND mpv.FacilityId = @OrderFacilityId
			AND mpv.PatientProfileId = @OrderPatientProfileId  -- 22196
	END
	ELSE
	BEGIN
		IF (@OrderApptId IS NOT NULL) AND (@OrderApptId > 0)
		BEGIN
			IF @Debug = 1
			BEGIN
				PRINT 'Look for Order Appt Id = ' + CONVERT(varchar, @OrderApptId) + ' in MIKPatientVisit'
			END

			SELECT @aMIKPatientVisitId = mpv.MIKPatientVisitId,
				@VisitDOS = mpv.DateOfService	-- SPR 13272 Set the VisitDOS
				FROM MIKPatientVisit mpv
				WHERE mpv.AppointmentsId = @OrderApptId
				AND mpv.DoctorId = @OrderDoctorId
				AND mpv.SupervisingDoctorId = @OrderAuthByDoctorId
				AND mpv.FacilityId = @OrderFacilityId
				AND mpv.PatientProfileId = @OrderPatientProfileId  -- 22196

			-- If the VisitDOS is NULL this indicates the the visit has not yet been created
			-- for this visit so we need to default it to the appointments date and time
			IF(@VisitDOS is NULL)
			begin
				set @VisitDOS = (select ApptStart from dbo.Appointments where AppointmentsId = @OrderApptId)
			end
		END
		ELSE IF(@OrderSDID is not NULL) -- BEGIN SPR 13272 On the fly update with orders
		BEGIN
			-- This is an on-the-fly order so we need to determine if a visit for the DOS
			-- has already been established. First we check if the orderdate to see if it is on
			-- the same day as that of the update document
			IF(@datetime1 <> @datetime2)
			begin
				-- No patient visit to associate to this since it is considered on the fly
				set @aMIKPatientVisitId = NULL

				-- The order date is older than the current date (i.e. in the past) we do not want to use the sign date of the 
				-- order, we want to use the order's DOS and the time of the Document
				IF(CONVERT(varchar,@OrderDate,101) < CONVERT(varchar, GetDate(), 101))
				begin
					set @VisitDOS = @OrderDate -- Set VisitDOS to order date and the time of the order document
					set @VisitDOS = DATEADD(hh, DATEPART(hh, dbo.Convert_ID_to_date(@OrderDocumentDate)), @VisitDOS)
					set @VisitDOS = DATEADD(mi, DATEPART(mi, dbo.Convert_ID_to_date(@OrderDocumentDate)), @VisitDOS)
				end
				ELSE
				begin
					-- For any on the fly charges for today or later use the time we signed the order
					set @VisitDOS = dbo.convert_id_to_date(@OrderLinkId) -- Set VisitDOS to time order was signed
				end
			end
			ELSE
			begin
				-- Set the visit for the date time when the document was created
				set @VisitDOS = dbo.Convert_ID_to_date(@OrderDocumentDate) -- Set the VisitDOS to the clinical document date time
			
				-- Attempt to find a visit based on the following Order criteria when
				-- the order date and document DOS are on the SAME day:
				--  1. DoctorId
				--  2. Authorizing DoctorId
				--  3. FacilityId
				--  4. DOS of the associated order document		
				--  5. PatientProfileId											
				SELECT @aMIKPatientVisitId = mpv.MIKPatientVisitId
					FROM MIKPatientVisit mpv
					WHERE mpv.DoctorId = @OrderDoctorId
					AND mpv.SupervisingDoctorId = @OrderAuthByDoctorId
					AND mpv.FacilityId = @OrderFacilityId
					AND mpv.DateOfService = @VisitDOS	
				    	AND mpv.PatientProfileId = @OrderPatientProfileId  -- 22196				
			end
		END
		-- END SPR 13272
	END

	-- SPR 16114
	-- Check for an existing Patient visit that is on the same day as the order	
	IF(@datetime1 = @datetime2)
	begin
		-- If no MIKPatientVisit was found yet then look for an existing Patient Visit
		IF((@aMIKPatientVisitId is NULL))
		begin
			set @ApprvVisitDOS = (DATEADD(second, -(SELECT DATEPART(second, @VisitDOS)), @VisitDOS))

			-- Attempt to find an EXISTING visit based on the following Order criteria 
			-- and has a billing status of New, In Progress, or On Hold:
			--  1. DoctorId
			--  2. Authorizing DoctorId
			--  3. FacilityId
			--  4. DOS of the associated order document	
			--  5. PatientProfileId													
			SELECT @OrderVisitId = pv.PatientVisitId
				FROM PatientVisit pv
				WHERE pv.DoctorId = @OrderDoctorId
				AND pv.SupervisingDoctorId = @OrderAuthByDoctorId
				AND pv.FacilityId = @OrderFacilityId
				AND (pv.Visit = @VisitDOS or pv.Visit = @ApprvVisitDOS)
				AND pv.PatientProfileId = @OrderPatientProfileId  -- 22196
		end
		ELSE
		begin
			set @OrderVisitId = (select PatientVisitId from MIKPatientVisit where MIKPatientVisitId=@aMIKPatientVisitId)
		end
	end


	/**
	 At this point, it is possible that we have non-null @OrderVisitId (which could have
	 been found from an appointment linked to the order document) but still have null for
	 @aMIKPatientVisitId.  In this case, we must make sure the doctor associated with the
	 existing visit is the same as the authorizing provider for the newly completed order.
	 **/
	IF (@OrderVisitId IS NOT NULL) AND (@OrderVisitId > 0)
		AND EXISTS (SELECT *
			FROM PatientVisit pv WHERE pv.PatientVisitId = @OrderVisitId
			AND (	pv.DoctorId <> @OrderDoctorId
				 OR pv.SupervisingDoctorId <> @OrderAuthByDoctorId) )
	BEGIN
		-- Force use of new visit because authorizing provider for order
		-- is different than doctor for existing visit.
		SET @OrderVisitId = NULL
	END

	-- SPR 13272 If the VisitDOS has not been set default it to current date for on-the-fly
	if ((@VisitDOS is NULL) and (@OrderVisitId is NULL))
	begin
		set @VisitDOS = GetDate()
	end
	else if(@OrderVisitId is not NULL)
	begin
		-- If we have a valid visit id then get the visit date and time
		set @VisitDOS = (select Visit from PatientVisit where PatientVisitId = @OrderVisitId)
	end

	SELECT @aMIKPatientVisitId AS MIKPatientVisitId,
			@OrderVisitId AS OrderVisitId,
			@OrderApptId AS OrderApptId,
			@VisitDOS AS VisitDOS -- SPR 13272

	SET NOCOUNT OFF
END -- End OrdGetVisit stored procedure

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF