USE [STATION]
GO
/****** Object:  StoredProcedure [dbo].[Nurse_SaveLabMaster]    Script Date: 09/03/2019 11:08:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Nurse_SaveLabMaster]
	@Hospnum As Varchar(20),
	@IDNum As Varchar(10),
	@ItemID As Varchar(8),
	@Amount As Float,
    @Quantity float,
	@RevenueID as varchar(2),
	@UserID As Varchar(8),
	@RoomId varchar(8),
	@DoctorID As Varchar(4),
	@DoctorName varchar(50),
	@CSNum varchar(30),
	@SpecimenId varchar(10) = ''
			
AS
DECLARE @RequestNum varchar(30),
	    @SectionID varchar(3),
        @Rush varchar(1),
	    @Transplant varchar(1),
	    @Dialysis varchar(1),
        @ORNum varchar(12),
	    @Counter int,
	    @Form varchar(1),
	    @IsProcedure bit,
	    @WithSurcharge bit,	
		@DepartmentID as varchar(2),
		@Barcode varchar(30),
		@NewBarcode varchar(30),
		@BCRequestNum varchar(12),
		@CharLength as int,
		@ErrorCode int,
		@BarcodeId as varchar(5)
SET @ErrorCode = 0

Select 	@SectionId = LabSectionID, 
		@Form = Form,
		@IsProcedure = IsProcedure
From Build_File..tbCoLabExam 
Where @ItemID = LabExamID;

if len(@SpecimenId) = 0	
	Begin
		set @SpecimenId = (case when isnull(@Specimenid,'') = '' then (select top 1 Specimenid from build_file..tbcolabspecimenexam where labexamid = @ItemID) 
							else @SpecimenId end);
	End
	
set @RoomId =  (case when isnumeric(@IdNum) = 1  then (Select RoomId from Patient_Data..tbPatient where IdNum = @IdNum) Else 'OPD' end);
set @BarcodeId = Isnull ((select BarcodeId from build_file..tbcolabsection where LabSectionID = @SectionId),'');
set @Rush = 'N'
set @Dialysis = 0
set @Transplant = 0

if @Form = 'C' begin
	 /* **************** Assign RequestNum to Variables  ****************** */
	declare @ProfAmount float,
			@ProfExamID varchar(4),
			@ProfSpecimenID varchar(4),
			@ProfSectionID varchar(4),
			@ProfBarcodeID varchar(1),
			@ProfBarcode varchar(30)
			
	declare curSaveProfile cursor fast_forward for
	select isnull(B.RateA,''), isnull(A.ExamID,''), isnull(A.SpecimenID,''), isnull(B.LabSectionID,''), isnull(C.BarcodeID,'')
	from BUILD_FILE..tbCoLabProfileExams A
		left outer join Build_File..tbCoLabExam B on B.LabExamID = A.ExamID
		left outer join Build_File..tbCoLabSection C on C.LabSectionID = B.LabSectionID
	where ProfileId = @ItemID

	open curSaveProfile
	fetch next from curSaveProfile into @ProfAmount, @ProfExamID, @ProfSpecimenID, @ProfSectionID, @ProfBarcodeID
	while (@@Fetch_Status=0) begin
		
		update Laboratory..tbLabSlip
    		set RequestNum=RequestNum + 1;            /* ===> Update RequestNum(aka LabNumber) on Each Saving Loop */
                             
							 set @ErrorCode = @@ERROR;
                             if @ErrorCode <> 0
                             return @ErrorCode;

		set @RequestNum=(select rtrim(cast(RequestNum as varchar(12))) from LABORATORY..tbLabSlip);
		set @ProfBarcode = @ProfBarcodeID + @CSNum + @ProfSpecimenID
		set @CharLength = (select datalength(@ProfBarcode))

			--if @CharLength = 7			set  @ProfBarcode = 'XXXXX'+ @ProfBarcode
			--else if @CharLength = 8		set  @ProfBarcode = 'XXXX'+ @ProfBarcode
			--else if @CharLength = 9		set  @ProfBarcode = 'XXX' + @ProfBarcode
			--else if @CharLength = 10	set  @ProfBarcode = 'XX'+ @ProfBarcode
			--else if @CharLength = 11	set  @ProfBarcode = 'X'+ @ProfBarcode
			--else  set  @ProfBarcode = @ProfBarcode
		
		if exists(select 1 from Laboratory..tbLabMaster where ItemID = @ProfExamID and RefNum = @CSNum and requestnum = @RequestNum) begin
			update Laboratory..tbLABMaster set RequestStatus = 'X', ORNum = @ORNum
			where ItemID = @ProfExamID and RefNum = @CSNum

			update Laboratory..tbLabBarcodingMaster set RequestStatus = 'X'
			where ItemID = @ProfExamID and RefNum = @CSNum
		end

		else begin

			insert into Laboratory..tbLabMaster (HospNum, IDNum, ItemID, Amount, TransDate, DoctorID, RequestStatus,
				PatientType, RoomID, UserID, RefNum, Rush, SectionID, Quantity, NurseRemarks, Transplant, Dialysis,
				SpecimenID, ORNum, ProfileID, OutsideDoctor, WithSurcharge, ItemCharged, Barcode)
	    	values (@HospNum, @IDNum, @ProfExamID, @ProfAmount, getdate(), @DoctorID, 'X',
				'O', @RoomId, @UserID, @CSNum, @Rush, @ProfSectionID, '1', '', @Transplant, @Dialysis,
				@ProfSpecimenID, @ORNum, @ItemID, @DoctorName, @WithSurcharge, @ItemID, @ProfBarcode);
				
			select @BCRequestNum = RequestNum from Laboratory..tbLabMaster where RefNum = @CSNum and ItemID = @ProfExamID
		
			insert into Laboratory..tbLabBarcodingMaster (RequestNum, RefNum, IDNum, HospNum, Barcode, ItemID,
				SpecimenID, SectionID, ProfileID, CommStat, MsgCtrlID, ResultStatus, ReceivedByLIS, RequestStatus, DateTransmitted,LISId)
			values (@BCRequestNum, @CSNum, @IDNum, @HospNum, @ProfBarcode, @ProfExamID,
				@ProfSpecimenID, @ProfSectionID, @ItemId, 'X', null, 'X', 'N', 'X', getdate(),'HCLAB')
		end

                                set @ErrorCode = @@ERROR;
                                if @ErrorCode <> 0
                                return @ErrorCode;

		fetch next from curSaveProfile into @ProfAmount, @ProfExamId, @ProfSpecimenID, @ProfSectionID, @ProfBarcodeID
	end;
	close curSaveProfile
	deallocate curSaveProfile			    
end
else begin
	if @IsProcedure = 0 
		begin
			set @Amount = @Amount /  @Quantity;
			set @counter = 1
			
			while @Counter <= convert(int, @Quantity)
				begin 
					update Laboratory..tbLabSlip
    				set RequestNum=RequestNum + 1; /* ===> Update RequestNum(aka LabNumber) on Each Saving Loop */

						set @ErrorCode = @@ERROR;
						if @ErrorCode <> 0
						return @ErrorCode;

					 /* **************** Assign RequestNum to Variables  ****************** */
					Set @RequestNum=(Select RTrim(Cast(RequestNum as varchar(12))) From LABORATORY..tbLabSlip);

	    			/* ************ Append to Laboratory Master File ****************************** */

						set @Barcode = (select isnull(@BarcodeId,'') + isnull(@CSNum,'') + isnull(isnull(@SpecimenId,0),''))
						
						set @CharLength = (select datalength(@Barcode))

						--if @CharLength = 7			set  @Barcode = 'XXXXX'+ @Barcode
						--else if @CharLength = 8		set  @Barcode = 'XXXX'+ @Barcode
						--else if @CharLength = 9		set  @Barcode = 'XXX' + @Barcode
						--else if @CharLength = 10	set  @Barcode = 'XX'+ @Barcode
						--else if @CharLength = 11	set  @Barcode = 'X'+ @Barcode
						--else  set  @Barcode = @Barcode
						
					if exists(select 1 from Laboratory..tbLabMaster where ItemID = @ItemID and RefNum = @CSNum and requestnum = @RequestNum) 
						begin
							update Laboratory..tbLABMaster set RequestStatus = 'X', ORNum = @ORNum
							where ItemID = @ItemID and RefNum = @CSNum

							update Laboratory..tbLabBarcodingMaster set RequestStatus = 'X'
							where ItemID = @ItemID and RefNum = @CSNum
						end

					else 
						begin
							insert into Laboratory..tbLabMaster (HospNum, IDNum, ItemID, Amount, TransDate, DoctorID, RequestStatus,
								PatientType, RoomID, UserID, RefNum, Rush, SectionID, Quantity, NurseRemarks, Transplant, Dialysis,
								SpecimenId, ORNum, OutsideDoctor, WithSurcharge, ItemCharged, Barcode)
	    					Values (@HospNum, @IDNum, @ItemID, @Amount, getdate(), @DoctorID, 'X',
								'O', @RoomId, @UserID, @CSNum, @Rush, @SectionID, @Quantity, '', @Transplant, @Dialysis,
								@SpecimenId, @ORNum, @DoctorName, @WithSurcharge, @ItemID, @Barcode);
								
							select @BCRequestNum = RequestNum from Laboratory..tbLabMaster where RefNum = @CSNum and ItemID = @ItemID
								
							Insert Into Laboratory..tbLabBarcodingMaster (RequestNum, RefNum, IDNum, HospNum, Barcode, ItemID,
								SpecimenID, SectionID, ProfileID, CommStat, MsgCtrlID, ResultStatus, ReceivedByLIS, RequestStatus, DateTransmitted,LISId)
							Values (@BCRequestNum, @CSNum, @IDNum, @HospNum, @Barcode, @ItemID,
								@SpecimenID, @SectionID, null, 'X', null, 'X', 'N', 'X', getdate(),'HCLAB')
						end

						set @ErrorCode = @@ERROR;
						if @ErrorCode <> 0
						return @ErrorCode;
						
					set @Counter = @Counter + 1
					continue;
				end
			end
	else 
		begin -- @IsProcedure = 1		  
			set @Amount = @Amount /  @Quantity;
			set @counter = 1
			
			while @Counter <= convert(int, @Quantity) 
				begin
					update Laboratory..tbLabSlip
					set RequestNum=RequestNum + 1; /* ===> Update RequestNum(aka LabNumber) on Each Saving Loop */

							set @ErrorCode = @@ERROR;
							if @ErrorCode <> 0
							return @ErrorCode;

					/* **************** Assign RequestNum to Variables  ****************** */
	    			set @RequestNum=(select rtrim(cast(RequestNum as varchar(12))) from LABORATORY..tbLabSlip);

    				/* ************ Append to Laboratory Master File ****************************** */
					set @Barcode = (select isnull(@BarcodeId,'') + isnull(@CSNum,'') + isnull(isnull(@SpecimenId,0),''))
					
					SET @NewBarcode = @Barcode + 'X' +  convert(varchar(3),@Counter);
					
					--set @CharLength = (select datalength(@Barcode))

					--if @CharLength = 7			set  @Barcode = 'XXXXX'+ @Barcode
					--else if @CharLength = 8		set  @Barcode = 'XXXX'+ @Barcode
					--else if @CharLength = 9		set  @Barcode = 'XXX' + @Barcode
					--else if @CharLength = 10	set  @Barcode = 'XX'+ @Barcode
					--else if @CharLength = 11	set  @Barcode = 'X'+ @Barcode
					--else  set  @Barcode = @Barcode

					if exists(select 1 from Laboratory..tbLabMaster where ItemID = @ItemID and RefNum = @CSNum and requestnum = @RequestNum) 
						begin
							update Laboratory..tbLABMaster set RequestStatus = 'X', ORNum = @ORNum
							where ItemID = @ItemID and RefNum = @CSNum
				
							update Laboratory..tbLabBarcodingMaster set RequestStatus = 'X'
							where ItemID = @ItemID and RefNum = @CSNum
						end
					else 
						begin
		    				insert into LABORATORY..tbLABMaster (HospNum, IDNum, ItemID, Amount, TransDate, DoctorID, RequestStatus,
								PatientType, RoomID, UserID, RefNum, Rush, SectionID, Quantity, NurseRemarks, Transplant, Dialysis,
								SpecimenId, ORNum, OutsideDoctor, WithSurcharge, ItemCharged, Barcode, numberCharged,RecordCount)
		    				values (@HospNum, @IDNum, @ItemID, @Amount, getdate(), @DoctorID, 'X',
								'O', @RoomId, @UserID, @CSNum, @Rush, @SectionID, '1', '', @Transplant, @Dialysis,
								@SpecimenId, @ORNum, @DoctorName, @WithSurcharge, @ItemID, @NewBarcode,@Quantity,@Counter)
							
							--select @BCRequestNum = RequestNum from Laboratory..tbLabMaster where RefNum = @CSNum and ItemID = @ItemID
							
							   Set  @BCRequestNum = (select RequestNum from Laboratory..tbLabMaster 
								where RefNum = @CSNum and ItemID = @ItemID and RecordCount = @Counter )

							insert into LABORATORY..tbLabBarcodingMaster (RequestNum, RefNum, IDNum, HospNum, Barcode, ItemID,
								SpecimenID, SectionID, ProfileID, CommStat, MsgCtrlID, ResultStatus, ReceivedByLIS, RequestStatus, DateTransmitted,LISId,recordCount)
							values (@BCRequestNum, @CSNum, @IDNum, @HospNum, @NewBarcode, @ItemID,
								@SpecimenID, @SectionID, null, 'X', null, 'X', 'N', 'X', getdate(),'HCLAB',@Counter)
						end

						set @ErrorCode = @@ERROR;
						if @ErrorCode <> 0
						return @ErrorCode;

					set @Counter = @Counter + 1
					continue
   				end;
	end;
end;

set @ErrorCode = @@ERROR;
if @ErrorCode <> 0
return @ErrorCode;

return @ErrorCode;