USE [Laboratory]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_LabGetPrice]    Script Date: 9/24/2020 11:43:37 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



ALTER FUNCTION [dbo].[fn_LabGetPrice] (@IdNum as varchar(15), @ExamID varchar(15))  

RETURNS varchar(15) AS 
BEGIN
  declare @Price as varchar(15)
  if exists (Select IdNum FROM Patient_data..tbpatient  WHERE IdNum = @IdNum)
     BEGIN
     Set @Price = ( 
	               Select 
       						
						CASE WHEN isnull(C.InterNational,'') = 'Y' THEN
								Case When Room.RoomClassID = 'A' then
		     					      cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
             				     When Room.RoomClassId = 'B' then
		     					      cast(tbExam.RateB  + (tbExam.RateB * 0.10) as money)
	     						 When Room.RoomClassId = 'C' then	  
							          cast(tbExam.RateC  + (tbExam.RateC * 0.10) as money)
	     						 When Room.RoomClassId = 'D' then
							          cast(tbExam.RateD  + (tbExam.RateD * 0.10) as money)
							     When Room.RoomClassId = 'E' then
							          cast(tbExam.RateE  + (tbExam.RateE * 0.10) as money)
							     When Room.RoomClassId = 'F' then
							          cast(tbExam.RateF  + (tbExam.RateF * 0.10) as money)
							     When Room.RoomClassId = 'G' then
							          cast(tbExam.RateG  + (tbExam.RateG * 0.10) as money)
							     When Room.RoomClassId = 'H' then
							          cast(tbExam.RateH  + (tbExam.RateH * 0.10) as money)
						     Else 
	    						  cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
							 End
						Else
							Case When Room.RoomClassID = 'A' then
		     					      cast(tbExam.RateA as money)
             				     When Room.RoomClassId = 'B' then
		     					      cast(tbExam.RateB as money)
	     						 When Room.RoomClassId = 'C' then	  
							          cast(tbExam.RateC as money)
	     						 When Room.RoomClassId = 'D' then
							          cast(tbExam.RateD as money)
							     When Room.RoomClassId = 'E' then
							          cast(tbExam.RateE as money)
							     When Room.RoomClassId = 'F' then
							          cast(tbExam.RateF as money)
							     When Room.RoomClassId = 'G' then
							          cast(tbExam.RateG as money)
							     When Room.RoomClassId = 'H' then
							          cast(tbExam.RateF as money)
						     Else 
	    						 cast (tbExam.RateA as money)
							 End
						End as [Amount]
     					   From Patient_data..tbpatient Patient
       	                        Left Outer Join Build_File..tbCoRoom Room On Room.RoomID = Patient.RoomID
       	                        Left Outer Join Build_File..tbCoLabExam tbexam on LabExamID = @ExamID
								Left Outer Join Build_File..tbCoCompany C  on C.AccountNum = Patient.AccountNumII
       					   Where Patient.Idnum = @IdNum 
       			
       			 
				)
      Return @Price
      END
      
   ELSE 
   
        Set @Price = ( select 
		                       
				
									CASE  WHEN isnull(C.InterNational,'') = 'Y' THEN
										cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
									ELSE
										Cast(tbExam.RateA as Money) 
									
								 
					    	END as [Amount]
     					   From Build_File..tbCoLabExam tbexam 
     					   Left Outer Join Patient_data..tboutpatient Patient on IDNum = @IdNum
						   Left Outer Join Build_File..tbCoCompany C on C.AccountNum = Patient.AccountNum
       					   Where LabExamID = @ExamID )
  		
		Return @Price
      
   
         
END