USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_SaveRequestForm]    Script Date: 11/21/2017 12:08:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_SaveRequestForm]
    @RevenueId varchar(10),
    @LabExamID varchar(10),
    @SpecimenID varchar(10),
    @UserId varchar(10),
    @SectionID varchar(10),
    @ProcessedBy varchar(100),
    @Stat varchar(10),
    @Account varchar(100),
    @RequestNum varchar(20),
    @NurseRemarks varchar(500),
    @DateRequested varchar(50),
    @Temp varchar(10) = '',
	@RefNum varchar(20) = ''

AS

-- ** Request Form Print-out Status - Y-1st Printout; D-Duplicate Copy; blank/null-Not yet Printed
Declare @PrintStatus varchar(1),
		@ProfileID varchar(10),
		@StationID varchar(10),
		@QtyIssued int,
		@SQL varchar(1000),
		@OPService bit,
		@CashRemarks as varchar(1000),
		@Itemid as varchar(10),
		@ReqDocId as varchar(10),
		@RequestDate datetime,
		@ClerkID varchar(10),
		@Form varchar(5),
		@pExamID varchar(10),
		@pSpecimenId varchar(10),
		@NurseRequestNum varchar(20),
		@IdNum varchar(20)

Set @PrintStatus ='';
Set @OPService = 0;

If @RequestNum like 'LB%' 
   Begin
		Select Top 1
			 @NurseRemarks = IsNull(Remarks,''),
			 @RequestDate = RequestDate,
			 @StationId = IsNull(StationId,''),
			 @ReqDocId = IsNull(DoctorId,''),
			 @ClerkId = IsNull(UserId,''),
			 @IdNum = IsNull(IdNum,'')
		From STATION..tbNurseCommunicationfile
		Where RequestNum = @RequestNum;
		
		Set @QtyIssued = IsNull((Select top 1 Quantity From STATION..tbNurseCommunicationfile where RequestNum = @RequestNum),1)
		Set @PrintStatus = IsNull((Select top 1 RequestFormStatus From tbLabMaster where NurseRequestNum = @RequestNum),'');
		Set @ProfileId = IsNull((Select top 1 ProfileId From tbLabMaster where NurseRequestNum = @RequestNum),'');
		set @RefNum = IsNull(@RefNum,@IdNum)
		Set @RequestNum = (Select top 1 RequestNum From tbLabMaster where NurseRequestNum = @RequestNum);
   End
Else
   Begin
		Select Top 1
			@RequestDate = Transdate,
			@NurseRemarks = IsNull(NurseRemarks,''),
			@StationId = IsNull(R.StationId,''),
			@ProfileId = IsNull(ProfileID,''),
			@ItemId = IsNull(Itemid,'') ,
			@PrintStatus=isnull(RequestFormStatus,''),
			@ClerkID = UserId,
			@ReqDocId = DoctorId
		From tbLabMaster L
			left outer join BUILD_FILE..tbCoRoom R on L.RoomID = R.RoomId
		Where Requestnum = @Requestnum
  
		
		Set @QtyIssued = IsNull((Select top 1 Quantity From tbLabMaster where RequestNum = @RequestNum),1)
		Set @CashRemarks = (select top 1 isnull(Remarks,'') from billing..tbcashassessment 
							where refnum = @RefNum and itemid =@itemid)
		set @NurseRemarks  = (select isnull(@CashRemarks,@NurseRemarks))
   End

Set @Form = (Select IsNull(Form,'') From Build_File..tbCoLabExam where LabExamID = @LabExamID);

If @PrintStatus = 'Y' 
   Begin
    -- Duplicate Copy
		Set @SQL = 'Update tbLabMaster 
					Set RequestFormStatus = ''D'',    
					RequestFormPrintDate = getdate()'
			if IsNull(@ProfileID,'') = '' Set @SQL = @SQL + ' where RequestNum = ''' + @RequestNum + ''';' Else Set @SQL = @SQL + ' where ProfileId = ''' + @ProfileID + ''' and RefNum = ''' + @RefNum + ''';'
			execute (@SQL);
   End;
Else If @PrintStatus = ''
   Begin
    -- First Copy Printed
        Set @SQL = 'Update tbLabMaster 
					Set RequestFormStatus = ''Y'',    
					RequestFormPrintDate = getdate()'
		if IsNull(@ProfileID,'') = '' Set @SQL = @SQL + ' where RequestNum = ''' + @RequestNum + ''';' Else Set @SQL = @SQL + ' where ProfileId = ''' + @ProfileID + ''' and RefNum = ''' + @RefNum + ''';'
		execute (@SQL);
   End;

Begin Transaction;
	If @Form = 'C'
	   Begin
			Declare curProfile Cursor Fast_Forward For
				Select ExamID,  Case When FixedSpecimen = 1 Then SpecimenID Else @SpecimenID End
				From Build_File..tbCoLabProfileExams
				Where ProfileId = @LabExamID 
				
				Open curProfile;
				Fetch Next From curProfile
				Into @pExamId, @pSpecimenID;
				While @@Fetch_Status = 0
					Begin
						Insert Into tbLabRequestForm_Dump (LabExamID, SpecimenID, UserID, SectionId, ProcessedBy, Stat, Company,
							ReqDoctor, RequestDate, ClerkID, NurseRemarks, ProfileId, 
							PrintStatus, StationID, QtyIssued, RefNum)
						Values (@pExamId, @pSpecimenID, @UserID, @SectionId, @ProcessedBy, @Stat, @Account,
							@ReqDocID, @RequestDate, @ClerkID, @NurseRemarks, @LabExamID, 
							'Y', @StationID, @QtyIssued, @RefNum);
					
						Fetch Next From curProfile
						Into @pExamId, @pSpecimenId;
					End
				Close curProfile;
				DeAllocate curProfile;
			
	   End
	Else
		Begin
			Insert Into tbLabRequestForm_Dump (LabExamID, SpecimenID, UserID, SectionId, ProcessedBy, Stat, Company,
								ReqDoctor, RequestDate, ClerkID, PrintStatus, ProfileId, NurseRemarks, StationID, 
								QtyIssued, OPService, RequestNum, RefNum)
			Values (@LabExamId, @SpecimenID, @UserID, @SectionId, @ProcessedBy, @Stat, @Account,
								@ReqDocID, @RequestDate, @ClerkID, @PrintStatus, @ProfileId, @NurseRemarks, @StationId, 
								@QtyIssued, @OPService, @RequestNum, @RefNum);
		End

if @@Error<> 0 
	Begin		
		Raiserror('Error(s) detected',16,2);
		Rollback Transaction;
	End
else
	Begin
		Commit Transaction;
	End
							
If @RefNum like 'C%I' 
   Begin
		Update Prepaid..tbPrepaidCharges
		Set PrintStatus = 1
		Where ChargeSlip = @Refnum and Revenueid = 'LB';
   End