USE [LABORATORY]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_LabGetPrice]    Script Date: 11/21/2017 12:39:01 ******/
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 (select Isnull(Class,'') as Class from Build_file..tbcocompany where Accountnum=Patient.Accountnum) not in ('I','H','P') then
      
       						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)
						     Else 
	    						 cast (tbExam.RateB as money)
	    				     End 
	    		   ELSE
	    		    
	    						 cast (tbExam.rateH as money)
	    					 
						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
       					   Where Patient.Idnum = @IdNum 
       			
       			 
				)
      Return @Price
      END
      
   ELSE 
     
   


    Set @Price = ( Select 
						Case When (select Isnull(Class,'') as Class from Build_file..tbcocompany where Accountnum=Patient.Accountnum) not in ('I','H','P') then
						cast (tbExam.RateA as money) 
						else
						cast (tbExam.RateH as money) 
						End as [Amount]
     					   From Patient_data..tboutpatient Patient
     					   left outer join  Build_File..tbCoLabExam tbexam on LabExamID = @ExamID
       					   Where Patient.Idnum = @IdNum 
       					    )
 




Return @Price
      
   
   
         
END