use Station

GO

alter table Station..tbPxICDRVS 
add  FromDM varchar(10) null

GO

use Doctors

GO

alter table Doctors..Doc_Orders
add  EmployeeID varchar(10) null

alter table Doctors..Doc_Orders
add UserID varchar(10) null

GO


/****** SP FOR SENDING PROFESSIONAL FEE ******/

USE [Station]
GO

/****** Object:  StoredProcedure [dbo].[sp_DocNet_SendPF]    Script Date: 12/07/2018 12:06:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[sp_DocNet_SendPF]
@idnum varchar(10),
@hospnum varchar(10),
@docID varchar(10),
@PF varchar(10),
@docname varchar(75),
@ordernum varchar(2),
@transdate datetime,
@icd varchar(10),
@icddesc varchar(250),
@dm varchar(10),
@orders varchar(250),
@refnum varchar(12),
@prevRefnum varchar(12),
@case varchar(8),
@isSCPWD varchar(1),
@scDiscountAmt varchar(6),
@withFD varchar(1),
@FinDiagFT varchar(500),
@remarks varchar(500)

as 

IF @icd <> '' 
    BEGIN
	    IF NOT EXISTS (SELECT * FROM Station..tbNurseProfFees WHERE IDNum = @idnum and DoctorID = @docID)
		   BEGIN
			 INSERT INTO Station..tbNurseProfFees (DoctorID, DoctorName, PF, OrderNumber, IDNUM)
			 VALUES (@docID, @docname, @PF, @ordernum, @idnum)
		   END
	    ELSE
		  BEGIN 
			 IF NOT EXISTS (SELECT 1 FROM Station..tbNurseDischargeNotice WHERE IDNum=@idnum AND DATALENGTH(MedicalFees) > 0)
				BEGIN 
				    UPDATE Station..tbNurseProfFees SET PF=@PF WHERE IDNUM=@idnum and DoctorID = @docID
				END
		  END

		  --check if there is an ICD or RVS for this patient
	    IF NOT EXISTS (SELECT * FROM Station..tbPxICDRVS WHERE IDNum=@idnum and (ICD=@icd or RVS=@icd))
		  BEGIN
			 IF @case = 'rvs' 
				BEGIN 
				    INSERT INTO Station..tbPxICDRVS (IDNum, RVS, RVSDESCRIPTION, FromDM, ICD, ICDDescription) 
				    VALUES (@idnum, @icd, @icddesc, @dm, '', '')
				END
			 ELSE
				BEGIN
				    INSERT INTO Station..tbPxICDRVS (IDNum, ICD, ICDDescription, FromDM) 
				    VALUES (@idnum, @icd, @icddesc, @dm)
				END 
		  END  

		  -- only ICD are to be saved in tbaptientdiagnosis
	   IF @case <> 'rvs'
		  BEGIN 
			 IF NOT EXISTS (SELECT IDNum FROM Patient_Data..tbpatientDiagnosis WHERE DiagnosisID=@icd and IDNum=@idnum)
				BEGIN
				    INSERT INTO Patient_Data..tbpatientDiagnosis (IDNum, DiagnosisID, PrimaryDiagnosis)
				    VALUES (@idnum, @icd, '')
				END
		  END 
        
        IF NOT EXISTS (SELECT * FROM Doctors..DOC_Orders WHERE EmployeeID=@icd AND DoctorID=@docID)
		  BEGIN 
			 INSERT INTO Doctors..DOC_Orders (IdNum, HospNum, DoctorID, TransDate, Orders,DocOrderType, UserID, EmployeeID)
			 VALUES (@idnum, @hospnum, @docID, @transdate, @orders, 0, @docID, @icd)
		  END
			 --this is for the saving of PF directly to tbbilldailybill
	   IF EXISTS (SELECT 1 FROM Station..tbNurseDischargeNotice WHERE IDNum=@idnum AND DATALENGTH(MedicalFees) > 0) --enter when final discharge order has been sent
		  BEGIN
			 IF NOT EXISTS (SELECT 1 FROM Billing..tbBillDailyBill WHERE IdNum=@idnum AND RevenueID='MD' AND ItemID=@docID AND Amount=@PF AND RefNum='C'+@refnum+'DM')
				BEGIN
				    INSERT INTO Billing..tbBillDailyBill (IdNum, TransDate, RevenueID, ItemID, RefNum, Amount, UserID)
				    VALUES (@idnum, GETDATE(), 'MD', @docID, 'C'+@refnum+'DM', @PF, @docID)
				END
		  END
	    ELSE
		  BEGIN -- check if patient already has an entry in tbbilldailybill
			 IF NOT EXISTS (SELECT 1 FROM Billing..tbBillDailyBill WHERE IdNum=@idnum AND RevenueID='MD' AND ItemID=@docID) 
				BEGIN
				    INSERT INTO Billing..tbBillDailyBill (IdNum, TransDate, RevenueID, ItemID, RefNum, Amount, UserID)
				    VALUES (@idnum, GETDATE(), 'MD', @docID, 'C'+@refnum+'DM', @PF, @docID)
				END
			 ElSE -- if patient has an entry for PF in tbbilldailybill, update the existing
				BEGIN
				    UPDATE Billing..tbBillDailyBill SET Amount=@PF WHERE ItemID=@docID AND IdNum=@idnum AND RevenueID='MD' AND RefNum='C'+@prevRefnum+'DM'
				END
		  END
	    --ELSE
		  --BEGIN
			-- UPDATE Billing..tbBillDailyBill SET Amount = @PF WHERE ItemID=@docID AND IdNum=@idnum
		 -- END
	   IF @isSCPWD = '1'
		  BEGIN
			 IF NOT EXISTS (SELECT 1 FROM Billing..tbBillDailyBill WHERE IdNum=@idnum AND RevenueID='SM' AND ItemID=@docID)
				BEGIN
				    INSERT INTO Billing..tbBillDailyBill (IdNum, TransDate, RevenueID, ItemID, RefNum, Amount,	 UserID)
				    VALUES (@idnum, GETDATE(), 'SM', @docID, 'C'+@refnum+'DM', @scDiscountAmt, @docID)
				END
			 ELSE
				BEGIN
				    UPDATE Billing..tbBillDailyBill SET Amount=@scDiscountAmt WHERE ItemID=@docID AND IdNum=@idnum AND RevenueID='SM'
				END
		  END
		  
	   -- this is for the saving of the free text final diagnosis
	   
	   --IF @withFD = '1'
		  --BEGIN 
			 --IF EXISTS (SELECT 1 FROM Patient_Data..tbpatientHistory WHERE IDNum=@idnum)
				--BEGIN
				--    UPDATE Patient_Data..tbpatientHistory SET FinalDiagnosis = @FinDiagFT WHERE IDNum= @idnum --udpating of final diagnosis
				--    INSERT INTO Patient_Data..tbAdmPatientInfoUpdate (Hospnum, IDNum, TransDate, UserID, Remarks, Module)
				--    VALUES (@hospnum, @idnum, GETDATE(), @docID, 'Updated FinalDiagnosis in Patient_Data..tbpatientHistory', 'Doctor`s Module')
				--END
			 --ELSE
				--BEGIN
				--    INSERT INTO Patient_Data..tbpatientHistory (IDNum, FinalDiagnosis)
				--    VALUES (@idnum, @FinDiagFT)
				--    INSERT INTO Patient_Data..tbAdmPatientInfoUpdate (Hospnum, IDNum, TransDate, UserID, Remarks, Module)
				--    VALUES (@hospnum, @idnum, GETDATE(), @docID, 'New entry for FinalDiagnosis in Patient_Data..tbpatientHistory', 'Doctor`s Module')
				--END
		  --END
		  
	   -- saving of remarks
	   
	   IF @remarks <> '' or @remarks is not null
		  BEGIN
			 IF NOT EXISTS (SELECT Remarks FROM Station..tbNurseDischargeNotice WHERE IDNum=@idnum)
				BEGIN 
				    INSERT INTO Station..tbNurseDischargeNotice (IDNum, Remarks)
				    VALUES (@idnum, @remarks)
				END
			 ELSE
				BEGIN 
				    UPDATE Station..tbNurseDischargeNotice SET Remarks = @remarks WHERE IDNum=@idnum
				END
		  END
    END
 
  
GO





/****** SP TO GET COMMON ICD PER DOCTOR ******/


USE [Patient_Data]
GO

/****** Object:  StoredProcedure [dbo].[sp_DocNet_GetICDEachDoctor]    Script Date: 11/22/2018 11:01:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[sp_DocNet_GetICDEachDoctor](
@DocID as varchar(10))
as  
SELECT TOP 10 diagnosisid [Code],
              COUNT(diagnosisid) AS DiagCount,
              MAX(CASER.Description) [Description],
              MAX(a.pf) [PF]
FROM Patient_Data..tbpatientDiagnosis AS DIAG WITH ( NOLOCK )
     RIGHT JOIN Medicare..tbMed_MedicalCaseRates AS CASER ON diag.DiagnosisID = caser.ICDCode
     LEFT OUTER JOIN Patient_Data..tbpatient AS PAT WITH ( NOLOCK ) ON diag.IDNum = pat.idnum
     RIGHT JOIN Medicare..tbMedPackage a ON caser.PackageID = a.PackageID
WHERE( AttendingDr1 = @DocID )
GROUP BY DiagnosisID
ORDER BY DiagCount DESC;

GO


USE [Patient_Data]

ALTER TABLE Patient_Data..tbMaster2  
ADD OccupationType varchar(50)
GO
	
ALTER TABLE Patient_Data..tbMaster2  
ADD WorkSchedule varchar(50)
GO


USE [Inventory]

ALTER TABLE Inventory..tbInvStockCard
ADD DoseStatus char(2) NULL GO

ALTER TABLE Inventory..tbInvStockCard
ADD EditedBy Varchar(100) NULL GO


USE [STATION]

GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [Motor] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [Verbal] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [EyeOpening] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [Right] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [Left] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [FIO2] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [TV] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [IERatio] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [MRate] [varchar](20) NULL
GO

ALTER TABLE Station..tbNurseVitalSigns
ADD [Peep] [varchar](20) NULL
GO


USE [Station]
GO

/****** Object:  Index [IxIDnumItemIDRefNum]    Script Date: 08/01/2018 17:26:32 ******/
CREATE NONCLUSTERED INDEX [IxIDnumItemIDRefNum] ON [dbo].[tbNurse_AdministeredMedicines] 
(
	[IDNum] ASC,
	[ItemID] ASC,
	[ReferenceNum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/****** for interpretation ******/

USE [Doctors]
GO

/****** Object:  StoredProcedure [dbo].[DocNet_GetPatientCardioExams]    Script Date: 12/12/2018 11:14:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



ALTER PROCEDURE [dbo].[DocNet_GetPatientCardioExams](
@Pv_HospNum AS Varchar(10)
)AS
select 
	a.requestnum as [Request #],
	b.hsexam [Exam Name],
	Patient_Data.dbo.Date_To_String(a.TransDate) AS [Trans. Date],
	a.formtype AS [FormType],
	Patient_Data.dbo.Full_Name(c.Lastname,c.firstname, c.Middlename) AS [Attending Doctor],
	a.TreadMillType, e.Interpretation
from 
	CARDIO..tbhrtlogbook a
	left outer join Build_File..tbCoHsExam b 
	on a.itemid = b.hsexamid
	LEFT OUTER JOIN Build_File..tbCoDoctor c 
	on a.doctorid = c.doctorid
	LEFT OUTER JOIN Build_File..tbCoDoctor d 
	on a.cardioid = d.doctorid
     LEFT OUTER JOIN Cardio..tbHrtResult e
     on a.RequestNum = e.RequestNum
where 
	a.HospNum =@Pv_Hospnum
order by 
	[TRANS. DATE] desc