USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[LAB_AppendLabMaster]    Script Date: 09/02/2019 7:56:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LAB_AppendLabMaster]
	@Hospnum AS VARCHAR(8),
	@IDNum AS VARCHAR(10),
	@Item AS VARCHAR(4),
	@Amount AS FLOAT,
	@UserID AS VARCHAR(8),
	@PatientType AS VARCHAR(1),	
	@RoomID AS VARCHAR(10),
	@DoctorID AS VARCHAR(4),
	@Rush VARCHAR(1),
	@RefNum VARCHAR(50),
	@Quantity FLOAT,
	@RequestDate DATETIME,
	@NurseRemarks TEXT,
	@Transplant VARCHAR(1),
	@Dialysis VARCHAR(1),
	@SpecimenId VARCHAR(4),
	@NurseRequestNum VARCHAR(10),
	@ProfileId VARCHAR(4) = NULL,
	@ProfId VARCHAR(10) = NULL,
	@ProfFee FLOAT = 0,
	@OutsideDoctor VARCHAR(50) = NULL,
	@DiscountType VARCHAR(2) = NULL,
	@NetAmount FLOAT = 0,
	@OPService BIT = 0,
	@ORNum VARCHAR(10) = NULL,
	@Remarks TEXT = ''

 AS

DECLARE @RequestNum VARCHAR(12)
DECLARE @SectionID VARCHAR(3),
		@Counter INT,
		@IsProcedure BIT,
		@ItemCharged VARCHAR(4),
		@WithSurcharge BIT

IF LEN(ISNULL(@ProfileId,'')) > 0 
   BEGIN	
		SET @SpecimenID = 
				(SELECT CASE WHEN A.FixedSpecimen = 1 THEN A.SpecimenID ELSE @SpecimenID END
					FROM BUILD_FILE..tbCoLabProfileExams A
						LEFT OUTER JOIN BUILD_FILE..tbCoLabExam B ON A.ExamID = B.LabExamId
   					WHERE A.ProfileId = @ProfileID AND A.ExamID = @Item)
   END;

SET @WithSurcharge = (CASE WHEN Laboratory.dbo.fn_LabAddMarkup (@RequestDate, @Item, @PatientType) = 'Y' THEN 1 ELSE 0 END);
SET @Transplant =	(CASE WHEN @Transplant = '-' THEN '0' ELSE @Transplant END);
SET @Dialysis =		(CASE WHEN @Dialysis = '-'	THEN '0' ELSE @Dialysis END);
SET @PatientType =	(CASE WHEN ISNUMERIC(@IdNum) = 1 THEN 'I' ELSE 'O' END);
SET @ItemCharged =	(CASE WHEN LTRIM(@ProfileID) = '' THEN @Item ELSE @ProfileID END);
SET @IsProcedure = ISNULL((SELECT IsProcedure FROM BUILD_FILE..tbCoLabExam
					WHERE LabExamId = @Item),0);


IF @RefNum LIKE 'C%%V'
	BEGIN
	/*********** For Packages posted using OPD Module **************/
		EXEC STATION..Nurse_SaveLabMaster @HospNum, @IDNum, @Item, @Amount, @Quantity,
											'LB',@UserId, @RoomId, @DoctorId, @OutsideDoctor, @RefNum, @SpecimenId

	END
ELSE
BEGIN
IF @IsProcedure = 0 
   BEGIN
   
REGENERATE_1:
		/* ===> Update RequestNum(aka LabNumber) on Each Saving Loop */
		UPDATE tbLabSlip
		SET RequestNum = RequestNum + 1; 
		
		/* **************** Assign RefNum and RequestNum to Variables  ****************** */
		SET @RequestNum=(SELECT RTRIM(CAST(RequestNum AS VARCHAR(12))) FROM tbLabSlip);
	    
		IF EXISTS(SELECT RequestNum FROM Laboratory..tbLabMaster WHERE Requestnum = @RequestNum)
			BEGIN
				GOTO REGENERATE_1
			END

		SET @SectionID= (SELECT DISTINCT LabSectionID FROM build_file..tbCoLabExam tbCoLabExam
						WHERE tbCoLabExam.LabExamID = LTRIM(RTRIM(@Item)));

		/* ************ Append to Laboratory Master File ****************************** */
		INSERT INTO tbLABMaster		
			  (HospNum, IDNum,  ItemID, Amount, TransDate, DoctorID,
		RequestStatus, PatientType, RoomID, UserID, RefNum, Rush, SectionID, Quantity, NurseRemarks,
		Transplant, Dialysis, SpecimenId, NurseRequestNum, ProfileID, ProfId, ProfFee, OutsideDoctor,
		ItemCharged, WithSurcharge, DiscountType, NetAmount, OPService, PhysicianID, OrNum, Remarks,numberCharged)
		VALUES			
			  (@HospNum, @IDNum,  @Item, @Amount, @RequestDate, @DoctorID, 'X', @PatientType, 
		@RoomID, @UserID, @RefNum, @Rush, @SectionID,@Quantity, @NurseRemarks,
		@Transplant, @Dialysis, @SpecimenId, @NurseRequestNum, @ProfileID, @ProfId, @ProfFee, @OutsideDoctor,
		@ItemCharged, @WithSurcharge, @DiscountType, @NetAmount, @OPService, @DoctorID, @ORNum, @Remarks,@Quantity);

  END

ELSE --@IsProcedure = 1
  BEGIN
    SET @Amount = @Amount/@Quantity;
	SET @counter = 1
	WHILE @Counter <= @Quantity
    	   BEGIN
		  
REGENERATE_2:
				/* ===> Update RequestNum(aka LabNumber) on Each Saving Loop */
				UPDATE tbLabSlip
				SET RequestNum = RequestNum + 1;
				
				/* **************** Assign RefNum and RequestNum to Variables  ****************** */
				SET @RequestNum=(SELECT RTRIM(CAST(RequestNum AS VARCHAR(12))) FROM tbLabSlip);
			    
				IF EXISTS(SELECT RequestNum FROM Laboratory..tbLabMaster WHERE Requestnum = @RequestNum)
					BEGIN
						GOTO REGENERATE_2
					END
 
    			SET @SectionID= (SELECT DISTINCT LabSectionID FROM build_file..tbCoLabExam tbCoLabExam
									WHERE tbCoLabExam.LabExamID = LTRIM(RTRIM(@Item)));

    			/* ************ Append to Laboratory Master File ****************************** */
    			INSERT INTO tbLABMaster		
          		   (HospNum, IDNum,  ItemID, Amount, TransDate, DoctorID,
				RequestStatus, PatientType, RoomID, UserID, RefNum, Rush, SectionID, Quantity, NurseRemarks,
				Transplant, Dialysis, SpecimenId, NurseRequestNum, ProfileID, ProfId, ProfFee, OutsideDoctor,
				ItemCharged, DiscountType, NetAmount, OPService, PhysicianID, OrNum, Remarks,numberCharged,RecordCount)
    			VALUES			
          		   (@HospNum, @IDNum, @Item, @Amount, @RequestDate, @DoctorID, 'X', @PatientType, 
				@RoomID, @UserID, @RefNum, @Rush, @SectionID,'1', @NurseRemarks,
				@Transplant, @Dialysis, @SpecimenId, @NurseRequestNum, @ProfileID, @ProfId, @ProfFee, @OutsideDoctor,
				@ItemCharged, @DiscountType, @NetAmount, @OPService, @DoctorID, @OrNum, @Remarks,@Quantity,@Counter);

    		   SET @Counter = @Counter + 1
    		   CONTINUE
			END
   END;
END