USE [RADIOLOGY]
GO
/****** Object:  StoredProcedure [dbo].[spRadio_SaveInCharge]    Script Date: 7/17/2023 1:29:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spRadio_SaveInCharge]
	@Type AS varChar(1), 
	@IDNum AS varChar(10), 
	@ItemID AS varChar(8),
	@TypeDescription as varchar(30), 
	@Quantity as float,
	@Amount AS Float, 
	@HospNum AS varChar(8),
	@DoctorID AS varChar(4),
	@RoomID AS varChar(8),
	@UserID AS varChar(10),
	@RefNum AS varChar(10),
	@Film1 as int,
	@Film2 as int,
	@Film3 as int,
	@Film4 as int,
	@Film5 as int,
	@Film6 as int,
	@Film7 as int,
	@NurseRemarks as varchar(50),
	@DoctorName as varchar(50) = null,
	@ChargeRevenueID as varchar(2) = '',
	@Portable as varchar(1) = '',
	@RevenueID AS varChar(2),
	@NurseRequestNum as varchar(10) = null,
	@Media varchar(150) = '',
	@Creatinine varchar(100) = '',
	@EKG varchar(50) = '',
	@Oximeter varchar(50) = '',
	@Particular varchar(200) = '',
	@Relevant varchar (200) = '',
	@Precaution varchar(200) = '',
	@ImagingReason varchar(500) = '',
	@SurgicalReason varchar(500) = '',
	@StatDesc varchar(30) = '',
	@StatDesc2 varchar(30) = '',
	@Remarks varchar(255) = ''
AS

DECLARE @RequestNum AS varChar(8), 
        @DrCr AS varChar(1),
		@UltrarevenueID as Varchar(2),
		@Suffix as varchar(5)--added by jecs

select @Suffix = RefNumSuffix FROM Radiology..tbRadiologyRevenues where GType = @Type--added by jecs

--added by jecs.start
SELECT @RequestNum = RTRIM(Convert(varChar(15), RequestNum)) + @Suffix 
FROM RADIOLOGY..tbradiologyrevenues 
where GType = @Type

UPDATE RADIOLOGY..tbradiologyrevenues 
set RequestNum = RequestNum + 1 
where GType = @Type
--added by jecs.end

If @DoctorID <> '0' 
	Begin
		set @DoctorName = null
	End;

IF @Type = '1'            /* CT-Scan */
     BEGIN
--*Commented below by jecs. Reason: replacement to fix no suffix issue upon charging
--		Rollback: just uncomment below and repaste per rev.
  --REGENERATE_1:
  --      SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbCTOpd
  --      UPDATE tbCTOpd SET RequestNum = RequestNum + 1
		
		--if exists(select requestnum from tbCTRequest where requestnum = @Requestnum)
		--	begin
		--		GOTO REGENERATE_1
		--	end

	 --added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbCTRequest WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end
        SET @DrCr = 'D'       

        INSERT INTO tbCTRequest 
            (RequestNum, TransDate, HospNum, IDNum,
                Code, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWFIN, [By], NurseRemarks, 
				DoctorName, revenueID, Portable, NurseRequest,
				Media,Creatinine, EKG, Oximeter,Particular,
				Relevant,Precaution,ImagingReason,SurgicalReason,Remarks)
         VALUES 
            (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, @NurseRemarks, 
				@DoctorName, @RevenueID, @Portable, @NurseRequestNum,
				@Media,@Creatinine, @EKG, @Oximeter, @Particular,
				@Relevant,@Precaution,@ImagingReason,@SurgicalReason,@Remarks)
     END
     
IF @Type = '2'            /* XRay */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbXRRequest WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end
        SET @DrCr = 'D'

        INSERT INTO tbXRRequest 
            (RequestNum, Transdate, HospNum, IDNum,
                Code, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWFIN, [By],
                Film1, Film2, Film3, Film4, Film5, Film6, Film7, 
				NurseRemarks, DoctorName, RevenueID, Portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES 
            (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID,
                @Film1, @Film2, @Film3, @Film4, @Film5, @Film6, @Film7, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks)
     END

IF @Type = '3'            /* Ultrasound */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbULRequest WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end


        INSERT INTO tbULRequest 
            (RequestNum, Transdate, HospNum, IDNum,
                Code, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWFIN, [By], 
				NurseRemarks, DoctorName, RevenueID, portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks) 
     END

IF @Type = '4'            /* MRI */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbMRIRequest WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end
        SET @DrCr = 'D'

        INSERT INTO tbMRIRequest 
            (RequestNum, Transdate, HospNum, IDNum,
                Code, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWFIN, [By], 
				NurseRemarks, DoctorName, RevenueID, portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks) 
     END

IF @Type = '5'            /* Mammogram */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbMammoMaster WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end

        SET @DrCr = 'D'

        INSERT INTO tbMammoMaster 
            (RequestNum, Transdate, HospNum, IDNum,
                ItemID, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWMRK, UserID, 
				NurseRemarks, DoctorName, RevenueID, portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks) 
     END

IF @Type = '6'            /* OB Ultrasound */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM RADIOLOGY..tbOBULRequest WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end
        SET @DrCr = 'D'

        INSERT INTO tbOBULRequest 
            (RequestNum, Transdate, HospNum, IDNum,
                Code, RequestDoctorCode, Amount,
                RoomID, RefNum, Quantity, SWFIN, [By], 
				NurseRemarks, DoctorName, RevenueID, portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks) 
     END

IF @Type = '7'            /* Nuclear */
     BEGIN
	--added by jecs.start
		WHILE EXISTS(SELECT RequestNum FROM Nuclear..tbNucMaster WHERE RequestNum = @RequestNum)
			BEGIN
				SET @RequestNum = @RequestNum + 1
			END
	--added by jecs.end

        SET @DrCr = 'D'

        INSERT INTO Nuclear..tbNucMaster 
            (RequestNum, Transdate, HospNum, IDNum,
                ItemID, DoctorId, Amount,
                RoomID, RefNum, Quantity, RequestStatus, UserId, 
				NurseRemarks, DoctorName, RevenueID, portable, 
				NurseRequest,ImagingReason,SurgicalReason,Remarks)
         VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, 
                @ItemID, @DoctorID, @Amount, 
                @RoomID, @RefNum, @Quantity, 'X', @UserID, 
				@NurseRemarks, @DoctorName, @RevenueID, @Portable, 
				@NurseRequestNum,@ImagingReason,@SurgicalReason,@Remarks) 
     END

INSERT INTO billing..tbBillDailyBill 
    (Refnum, IDNum, Transdate, ItemID, Quantity, Amount, 
        DrCr, RevenueID, RoomID, UserID, RequestDocID, RequestNum, DoctorName)
VALUES 
    (@Refnum, @IDNum, GetDate(), @ItemID, @Quantity, @Amount, 
        @DrCr, @RevenueID, @RoomID,@UserID, @DoctorID, @RequestNum, @DoctorName )

IF EXISTS(SELECT * FROM station..tbNurseLogBook WHERE 
                  IDNum = @IDNum --HospNum = @HospNum AND 
                  AND RevenueID = @RevenueID AND ItemID = @ItemID and isnull(RecordStatus,'') = '') -- RequestNum = @NurseRequestNum)
     BEGIN
            UPDATE station..tbNurseLogBook 
            SET	RecordStatus = 'X', 
		ProcessBy = @UserID, 
		ProcessDate = GETDATE(),
		ReferenceNum = @RefNum
            WHERE IDNum = @IDNum  --HospNum = @HospNum AND 
            AND RevenueID = @RevenueID AND ItemID = @ItemID and  isnull(RecordStatus,'') = '' --RequestNum = @NurseRequestNum
     END
ELSE
     BEGIN
             INSERT INTO station..tbNurseLogBook 
                (HospNum, IDNum, PatientType, 
                    RevenueID, RequestDate, 
                    ItemID, Description, Quantity, 
                    Amount, RecordStatus, 
                    ReferenceNum, ProcessBy, ProcessDate,Remarks)
             VALUES 
                (@HospNum, @IDNum, 'I', 
                    @RevenueID, GETDATE(),
                    @ItemID, @TypeDescription, @Quantity, 
                    @Amount, 'X', @RefNum, @UserID, GETDATE(),'Manualy Posted')
     END



IF EXISTS(SELECT * FROM station..tbNurseCommunicationFile WHERE 
                  IDNum = @IDNum --HospNum = @HospNum AND 
                  AND RevenueID = @RevenueID AND ItemID = @ItemID and isnull(RecordStatus,'') = '') -- RequestNum = @NurseRequestNum)
     BEGIN
            UPDATE station..tbNurseCommunicationFile 
            SET	RecordStatus = 'X', 
		ReferenceNum = @RefNum
            WHERE IDNum = @IDNum  --HospNum = @HospNum AND 
            AND RevenueID = @RevenueID AND ItemID = @ItemID and  isnull(RecordStatus,'') = '' --RequestNum = @NurseRequestNum
     END

-- * Insert only if post charges from Nursing Station
	IF EXISTS(SELECT * FROM station..tbNurseLogBook WHERE 
					  IDNum = @IDNum --HospNum = @HospNum AND 
					  AND RevenueID = @RevenueID AND ItemID = @ItemID and RequestNum = @NurseRequestNum)
		 BEGIN
				UPDATE station..tbNurseLogBook 
				SET	RecordStatus = 'X', 
			ProcessBy = @UserID, 
			ProcessDate = GETDATE(),
	   		ReferenceNum = @RefNum
				WHERE IDNum = @IDNum  --HospNum = @HospNum AND 
				AND RevenueID = @RevenueID AND ItemID = @ItemID and RequestNum= @NurseRequestNum
		 END