USE PATIENT_DATA


Go
Alter Table tbmedrecsetup Add
RevenueCode varchar(3)


-------------------------------------------------------------
Go
Update tbMedrecsetup
set RevenueCode = 'MC'

GO

Insert into Patient_Data..tbMedRecMenu(MenuID,Menu,Status,MenuGroup,MainMenu)
values('107','Assessment Patient List','1','C','2')
GO

-------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Sp_MedRec_IncomeStatement')
EXEC ('CREATE PROC dbo.Sp_MedRec_IncomeStatement AS SELECT 1')
GO

ALTER Procedure Sp_MedRec_IncomeStatement
@StartDate varchar(12),
@EndDate varchar(12)
As
--Declare 
--@StartDate varchar(12),
--@EndDate varchar(12)
--
--Set @StartDate ='04/01/2012'
--Set @EndDate ='04/09/2012'


Declare @RevenueID as varchar(3)
Select @RevenueID = RevenueCode from Patient_Data..tbMedRecSetup

Select A.RevenueID,
		A.IDNum,
		A.HospNum,
		A.Name,
		A.Transdate,
		A.AssessNum,
		A.Quantity,
		A.Amount,
		A.ItemID,A.RecordStatus,
		B.OtherRevenue,
		Case 
			when right(A.IDNum,1)= 'B' then 'OUTPATIENT'
			Else 'INPATIENT'
		End as PatientType
From Billing..tbCashAssessment A
Left Outer Join Build_File..tbcoOtherRevenue B on A.ItemID = B.OtherRevenueID
Where RevenueID = @RevenueID
And A.TransDate between @StartDate and @EndDate + ' 23:59:59:99'
And (A.RecordStatus <> 'R' or A.RecordStatus is null)
Order By B.OtherRevenue


-------------------------------------------------------------------------------
GO

Insert into Patient_Data..tbMedRecMenu(MenuID,Menu,Status,MenuGroup,MainMenu)
values('106','Assessment Income Report','1','C','2')

-------------------------------------------------------------------------------GO
go

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Sp_MedRec_AssesstmentReport')
EXEC ('CREATE PROC dbo.Sp_MedRec_AssesstmentReport AS SELECT 1')
GO



ALTER Procedure [dbo].[Sp_MedRec_AssesstmentReport]
@StartDate varchar(12),
@EndDate varchar(12)
As
--Declare 
--@StartDate varchar(12),
--@EndDate varchar(12)
--
--Set @StartDate ='04/01/2012'
--Set @EndDate ='04/09/2012'


Declare @RevenueID as varchar(3)
Select @RevenueID = RevenueCode from Patient_Data..tbMedRecSetup

Select A.RevenueID,
		A.IDNum,
		A.HospNum,
		A.Name,
		A.Transdate,
		A.AssessNum,
		A.Quantity,
		A.Amount,
		A.ItemID,A.RecordStatus,
		B.OtherRevenue,
		Case 
			when right(A.IDNum,1)= 'B' then 'OUTPATIENT'
			Else 'INPATIENT'
		End as PatientType,
		C.Lastname+', '+C.FirstName+' '+Left(C.MiddleName,1)as [User]
From Billing..tbCashAssessment A
Left Outer Join Build_File..tbcoOtherRevenue B on A.ItemID = B.OtherRevenueID
Left Outer Join Password..tbPasswordMaster C on A.UserID = C.employeeID
Where RevenueID = @RevenueID
And A.TransDate between @StartDate and @EndDate + ' 23:59:59:99'
And (A.RecordStatus <> 'R' or A.RecordStatus is null)
Order By B.OtherRevenue

-------------------------------------------------------------------------------
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_MedRec_PrintAssesstment')
EXEC ('CREATE PROC dbo.sp_MedRec_PrintAssesstment AS SELECT 1')
GO



ALTER  Procedure [dbo].[sp_MedRec_PrintAssesstment]
@AssestmentNum varchar(15)

as

select 
a.HospNum,
a.IdNum,
a.[Name]as PxName,
a.TransDate,
a.AssessNum,
a.ItemID,
a.Quantity,
a.Amount,
b.LastName+', '+ b.FirstName +' '+left(b.MiddleName,1)as Employee,
c.OtherRevenue 

From Billing..tbCashAssessment a
left outer join Password..tbpasswordmaster b on a.UserID = b.EmployeeID
left outer join Build_File..tbcoOtherRevenue c on a.ItemID = c.OtherRevenueID
--Where AssessNum = 'MR' + + RTRIM(@AssestmentNum)
Where AssessNum =  RTrim(@AssestmentNum)

--------------------------------------------------------------------------------
go




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_MedRec_AssessmentEntry')
EXEC ('CREATE PROC dbo.sp_MedRec_AssessmentEntry AS SELECT 1')
GO


ALTER PROCEDURE [dbo].[sp_MedRec_AssessmentEntry]

@strType as varchar(5),
@Name AS varChar(60),
@RevenueID as varchar(2),
@ItemID AS varChar(8), 
@Quantity AS Float,
@Amount AS Float,
@UserID AS varChar(10),
@IdNum as varchar(10),
@Hospnum as varchar(10),
@AssessNum AS varChar(10)

AS

DECLARE @AssessmentNum AS varChar(10), 
  @DrCr AS varChar(1)

--IF @RevenueID = 'MD'
--  BEGIN
     SET @DrCr = 'D'
--  END


SET @AssessmentNum = @strType + RTRIM(@AssessNum)

INSERT INTO Billing..tbCashAssessment (IDNum, [Name], Transdate, AssessNum, 
  [Indicator], DrCr, ItemID, Quantity, Amount, RevenueID, UserID, 
  DepartmentId, Hospnum, RefNum)  
VALUES (@IdNum, @Name, GETDATE(), @AssessmentNum, @RevenueID, @DrCr,
  @ItemID, @Quantity, @Amount, @RevenueID, @UserID, @strType, @Hospnum,
  @AssessmentNum)

GO
--------------------------------------------------------------------------------


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_ConsolidationList')
EXEC ('CREATE PROC dbo.sp_Adm_ConsolidationList AS SELECT 1')
GO


GO
ALTER PROCEDURE [dbo].[sp_Adm_ConsolidationList]
@LastName as varchar(40) = null

as
if @LastName = '' or @LastName  = null
	begin 
		Select distinct '', Hospnum, NewHospnum,
		 dbo.Fn_GetCompleteName(NewHospnum) PatientName,
		dbo.fn_GetCompleteAddress(NewHospnum) Address
		 from Patient_Data..tbPatient P
		left Outer Join Patient_Data..tbMergedHistory M
		on P.Hospnum = M.OldHospnum
		where hospnum in (
		Select oldhospnum from Patient_Data..tbmergedhistory) and
		Newhospnum in (Select hospnum from Patient_Data..tbmaster) 

		union all

		Select distinct '', Hospnum, NewHospnum,
		 dbo.Fn_GetCompleteName(NewHospnum),
		dbo.fn_GetCompleteAddress(NewHospnum)
		 from Patient_Data..tbOutPatient P
		left Outer Join Patient_Data..tbMergedHistory M
		on P.Hospnum = M.OldHospnum
		where hospnum in (
		Select oldhospnum from Patient_Data..tbmergedhistory) and
		Newhospnum in (Select hospnum from Patient_Data..tbmaster)
	End
Else
	begin 
		Select distinct '', P.Hospnum, M.NewHospnum,
			 dbo.Fn_GetCompleteName(M.NewHospnum) PatientName,
			dbo.fn_GetCompleteAddress(M.NewHospnum) Address
			 from Patient_Data..tbPatient P
			left Outer Join Patient_Data..tbMergedHistory M on P.Hospnum = M.OldHospnum
			left Outer Join Patient_Data..tbMaster Master on M.NewHospnum = Master.Hospnum
			where P.hospnum in (Select oldhospnum from Patient_Data..tbmergedhistory) 
			And M.Newhospnum in (Select hospnum from Patient_Data..tbmaster) 
			and Master.LastName like '%'+ @LastName +'%'


		Union All

		Select distinct '', P.Hospnum, M.NewHospnum,
			 dbo.Fn_GetCompleteName(M.NewHospnum) PatientName,
			dbo.fn_GetCompleteAddress(M.NewHospnum) Address
			 from Patient_Data..tbOutPatient P
			left Outer Join Patient_Data..tbMergedHistory M on P.Hospnum = M.OldHospnum
			left Outer Join Patient_Data..tbMaster Master on M.NewHospnum = Master.Hospnum
			where P.hospnum in (Select oldhospnum from Patient_Data..tbmergedhistory) 
			And M.Newhospnum in (Select hospnum from Patient_Data..tbmaster) 
			and Master.LastName = @LastName		And Master.LastName = @LastName
	End