USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_GetSelectedMedtech]    Script Date: 10/4/2019 12:14:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_GetResult_TextFormat]
@RequestNum as varchar(30)
AS

DECLARE   @Formtype VARCHAR(3),
	    @Valuetype VARCHAR(3),
	    @ExamId VARCHAR(10),
                  @ExamName VARCHAR(100)

set @ExamId = (select top 1 LabExamID from tblablogbook where Requestnum = @RequestNum );

SELECT TOP 1 
		@FormType = FormType,
		@ValueType = ValueType,
		@ExamName = ResultName
	FROM Build_File..tbcoLabValues
	WHERE Code = @ExamId;

IF @FormType IN ('1','7') -- Normal Value without Conversion
	BEGIN
		
	     IF EXISTS (SELECT 1 FROM tbLabResultNValues WHERE RequestNum = @RequestNum ) 
	         BEGIN 
			select (SELECT  @ExamName +CHAR(13)+
				           ' Result: ' + StrResult +' ' + Unit + CHAR(13)+
				           'Reference Range: ' + StrNValues + ' ' + Unit
                                                 FROM tbLabResultNValues 
                                                 WHERE RequestNum = @RequestNum )  as Result

					END
	         ELSE 
		BEGIN
		    Select 'No Result found.' as Result;
		END
					
				
				
	END

ELSE IF @FormType IN ('8','B') -- Normal Value with Conversion
	BEGIN
		   IF EXISTS (SELECT 1 FROM tbLabResultNValues WHERE RequestNum = @RequestNum ) 
	         BEGIN 
			select (SELECT  @ExamName +CHAR(13)+
				           'SI Result: ' + StrResult +' ' + Unit + CHAR(13)+
				           'Reference Range: ' + StrNValues + ' ' + Unit + CHAR(13)+
				           'Conventional Result: ' + ConvStrResult +' ' + ConvUnit + CHAR(13)+
				           'Reference Range: ' + ConvStrNValues + ' ' + ConvUnit
                                                 FROM tbLabResultNValues 
                                                 WHERE RequestNum = @RequestNum )  as Result

					END
	         ELSE 
		BEGIN
		    Select 'No Result found.' as Result;
		END
		

					
	END
ELSE IF @FormType IN ('2','3') 
	BEGIN -- Paragraph Format
				
                   IF EXISTS (SELECT 1 FROM tbLABResultShortForm  WHERE RequestNum = @RequestNum ) 
	         BEGIN 	
			select (SELECT  @ExamName + CHAR(13)+
				           ' Result: ' + RESULT  
                                                 FROM  tbLABResultShortForm
                                                 WHERE RequestNum = @RequestNum )  as Result

	      ELSE 
		BEGIN
		    Select 'No Result found.' as Result;
		END
		
 END

ELSE IF @FormType IN ('H') 
     BEGIN -- Hard Coded Format
	IF @ValueType = 'U'  --URINALYSIS
	    BEGIN
	      IF EXISTS (SELECT 1 FROM tblabResultHCodes WHERE Requestnum = @RequestNum)
		 BEGIN
			select (SELECT  @ExamName + CHAR(13)+
				            'Physical Examination'  + CHAR(13)+
				           '    Color: ' + Result1 + CHAR(13)+ 
				           '    Appearance: ' + Result2 + CHAR(13)+ 
				           '   pH: ' + Result3 + CHAR(13)+ 
				           '   Specific Gravity: ' + Result4 + CHAR(13)+ 
                                                                   'Chemical Examination'  + CHAR(13)+
                                                                   '   Albumin: ' + Result5 + CHAR(13)+ 
                                                                   '   Sugar: ' + Result6 + CHAR(13)+ 
				           '   Others: ' + Result6 + CHAR(13)+ 
                                                                   'Microscopic Examination'  + CHAR(13)+
                                                                   '   Pus Cells: ' + Result7 + CHAR(13)+ 
                                                                   '   Red Cells: ' + Result8 + CHAR(13)+ 
                                                                   '   Epithelial Cells: ' + Result9 + CHAR(13)+ 
                                                                   '   Mucus Threads: ' + Result10 + CHAR(13)+ 
                                                                   '   Bacteria: ' + Result11 + CHAR(13)+ 
                                                                   '   Amorphous Urates: ' + Result12 + CHAR(13)+ 
                                                                   '   Amorphous Phosphates: ' + Resul13 + CHAR(13)+ 
                                                                   '   Yeast Cells: ' + Result14 + CHAR(13)+ 
                                                                   '   Crystals: ' + Resul16 + CHAR(13)+ 
                                                                   '   Casts: ' + Result17 + CHAR(13)+ 
                                                                   ' Remarks: ' + Result20 + CHAR(13)+ 
                                                 FROM  tbLABResultShortForm
                                                 WHERE RequestNum = @RequestNum )  as Result

                             END
                     ELSE 
		BEGIN
		    Select 'No Result found.' as Result;
		END
	END
                ELSE IF @ValueType = 'S' --STOOL FORMAT
	    BEGIN
	      IF EXISTS (SELECT 1 FROM tblabResultHCodes WHERE Requestnum = @RequestNum)
		 BEGIN
			select (SELECT  @ExamName + CHAR(13)+
				           '    Color: ' + Result1 + CHAR(13)+ 
				           '    Consistency: ' + Result2 + CHAR(13)+ 
				           '    Occult Blood: ' + Result3 + CHAR(13)+ 
				           '    pH: ' + Result4 + CHAR(13)+ 
                                                                   '   Result: ' + Result5 + CHAR(13)+ 
                                                                   '   Comments: ' + Result20 + CHAR(13)+ 
                                                 FROM  tbLABResultShortForm
                                                 WHERE RequestNum = @RequestNum )  as Result

                             END
                        ELSE 
		BEGIN
		    Select 'No Result found.' as Result;
		END
 
	  END

END


ELSE IF @FormType IN ('P') -- Normal Value with Conversion
	BEGIN
	    IF @Valuetype IN ('1','7')
   		BEGIN
		   IF EXISTS (SELECT 1 FROM tbLabResultNValues WHERE RequestNum = @RequestNum ) 
	                       BEGIN 

			select @ExamName + CHAR(13)+stuff((SELECT ',' + ResultName +CHAR(13)+
				           'Result: ' + StrResult +' ' + a.Unit + CHAR(13)+
				           'Reference Range: ' + StrNValues + ' ' + a.Unit + CHAR(13)
                                                 FROM tbLabResultNValues a
			       left outer join  build_file..tbcolabvalues b on a.ItemID = b.code
                                                 WHERE RequestNum = @RequestNum
			     order by sortorder asc for XML PATH('')),1,1,'')  as Result

					END
	                 ELSE 
		    BEGIN
		    Select 'No Result found.' as Result;
		    END
		
 	        END
	    ELSE IF @Valuetype IN ('8','B')
   		BEGIN
		   IF EXISTS (SELECT 1 FROM tbLabResultNValues WHERE RequestNum = @RequestNum ) 
	                       BEGIN 


			select @ExamName + CHAR(13)+stuff((SELECT ',' + ResultName +CHAR(13)+
				           'SI Result: ' + StrResult +' ' + a.Unit + CHAR(13)+
				           'Reference Range: ' + StrNValues + ' ' + a.Unit + CHAR(13)
                                                                   'Conventional Result: ' + ConvStrResult +' ' + ConvUnit + CHAR(13)+
				           'Reference Range: ' + ConvStrNValues + ' ' + ConvUnit
                                                 FROM tbLabResultNValues a
			       left outer join  build_file..tbcolabvalues b on a.ItemID = b.code
                                                 WHERE RequestNum = @RequestNum
			     order by sortorder asc for XML PATH('')),1,1,'')  as Result

					END
	                 ELSE 
		    BEGIN
		    Select 'No Result found.' as Result;
		    END
		
 	        END				
	END