use Inventory
go

Create Procedure [dbo].[Delete_Generic]
			@GenericID varchar(5)

AS

Delete 
From tbForGeneric
where GenericID = @GenericID


Go

------------------------------------------------------------------------------

USE PATIENT_DATA

GO

alter table  Patient_Data.dbo.tbOutPatientHistory  alter column DiagnosisID1 varchar(15)
go
alter table  Patient_Data.dbo.tbOutPatientHistory  alter column DiagnosisID2 varchar(15)
go
alter table  Patient_Data.dbo.tbOutPatientHistory  alter column DiagnosisID3 varchar(15)
go
alter table  Patient_Data.dbo.tbOutPatientHistory  alter column DiagnosisID4 varchar(15)
go
alter table  Patient_Data.dbo.tbOutPatientHistory  alter column DiagnosisID5 varchar(15)
go

 
Alter Table PATIENT_DATA..tbhospitalinfo Add
Seafarer bit not null default(1)
GO
-------------------------------------------------------------------------------
GO

Alter Table Patient_Data..tboutPatient Add
isRehab bit not null default(0),
isOMP bit not null default(0),
isCPClearance bit not null default(0)
GO
-------------------------------------------------------------------------------
GO

Alter Table patient_Data..tboutpatient add
isPEME bit not null default(0),
isREPAT bit not null default(0)

-------------------------------------------------------------------------------
GO
Alter Table Patient_Data..tbOutPatient Add
	[SeamanDeptID] [int] NULL,
	[SeamanDeptOther] [varchar](50) NULL,
	[ShipType] [varchar](50) NULL,
	[TradeID] [int] NULL
GO
-------------------------------------------------------------------------------
GO

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

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


Use FIXEDASSET
go
ALTER INDEX [ASSETMANAGER] ON [tbFAAssetManager] SET (ALLOW_PAGE_LOCKS = ON)
go
ALTER INDEX [PK_MAJCAT] ON [tbFAMajorCategory] SET (ALLOW_PAGE_LOCKS = ON)
go


USE [Station]
GO

/****** Object:  StoredProcedure [dbo].[Print_Nurse_HomeCarePlan]    Script Date: 12/01/2013 03:28:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[Print_Nurse_HomeCarePlan]
--declare 

	@Idnum varchar(10)

AS 
--set @Idnum ='548524'

Select A.Idnum,
		--A.Patient,
		tM.lastname + ', ' + tm.firstname,
		A.Age,
		convert(varchar(10),A.Transdate,101) as TransDate,
		A.ActivityLevel,A.DietInstruction,
		A.Treatment,
		A.WhatToObserve,
		A.COnsideration,
		A.DescriptionID,
		A.DocName,
		COnvert(Varchar(10),A.DocDate,101) as DocDate,
		A.ReceivedBy,
		Convert(varchar(10),A.ReceivedDate,101) as ReceivedDate,
		A.GuardianName,
		A.DischargeNurse,
--a.*,
		b.medications, b.instructions
From Station..tbNurseHomeCarePlan A
	left outer join station..tbNurseDischargeInstructions b on a.idnum = b.idnum
	left outer join patient_data..tbpatient Patient on Patient.idnum = a.idnum
	left outer join patient_data..tbmaster TM on Tm.hospnum  = patient.hospnum
Where a.Idnum = @IdNum



GO



USE [Station]
GO

/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatientMedicines]    Script Date: 12/01/2013 03:24:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[sp_Nurse_GetPatientMedicines]
	@IDNum As Varchar(10)
AS

--set @IDNum	= '569232'

	BEGIN
			/*
		-- Insert Data to Station..tbNursePatientMedicines
			--BEGIN
				
				Insert Into Station..tbNursePatientMedicines(IDNum,ItemID,ItemName,DosageID,Quantity)
				 	
					Select	@IDNUm,IsNull(A.ItemID,'') [Drug Code],
						IsNull(B.ItemName,'') + ' ' + IsNull(B.ItemDesc,'') [Drug Name],
					        C.Dosage,
						Sum(Case 
							When IsNull(A.SummaryCode,'') = '' Then 0
							When IsNull(A.SummaryCode,'') = 'PH' and A.Quantity < 0 Then A.Quantity
							When IsNull(A.SummaryCode,'') = 'PH' and A.Quantity > 0 Then A.Quantity
							When IsNull(A.SummaryCode,'') = 'PC' Then A.Quantity * -1
						End) [Qty.]
					
					
					From INVENTORY..tbInvStockCard as A 
							Inner Join INVENTORY..tbInvMaster as B
								On A.ItemID = B.ItemID
							Left Outer Join INVENTORY..tbInvUnitDose C
								On A.Idnum = C.IDnum and A.ItemID = C.ItemID 
					Where  A.IdNum = @IDNUm and
					        ( A.RevenueID = 'PH' or A.RevenueID = 'PC' )
						and (Not A.ItemID IN (Select ItemID From Station..tbNursePatientMedicines Where IdNum = @IDNUm))
					Group By IsNull(B.ItemName,'') + ' ' + IsNull(B.ItemDesc,''),A.ItemID,C.Dosage
					Order By IsNull(B.ItemName,'') + ' ' + IsNull(B.ItemDesc,'');
						--and A.LocationID = '20'
				
			--END
			

		Select	A.ItemId As Code ,A.ItemName As Description,A.DosageID As Dosage,
			A.Quantity + Isnull(A.RecievedFromPatient,0) As Qty,
			Isnull((	Select Sum(Isnull(Quantity,0))
				From 	Station..tbNurse_AdministeredMedicines B
				Where B.IDNum = @IDNUm and B.ItemID = A.ItemID
					and B.RequestNum =Convert(Varchar(10),A.Transnum)--A.Transdate
			),0) As Administered,
			Convert(Varchar(10),A.Transnum) As RequestNum, --A.TransDate As RequestNum				
			A.RecievedFromPatient As Recieved,
			A.OrderedDateBydoctor As OrderedDate,
			(	Select TOP 1 Isnull(QuantityRec,0)
				From INVENTORY..tbInvStockCard 
				Where IDNum = A.IDNum and ItemID = A.ItemID and SummaryCode = 'PH'
					and Quantity > 0
				Order By Transdate DESC			
			) As Unused,
			(	Select TOP 1 SequenceNumber
				From INVENTORY..tbInvStockCard 
				Where IDNum = A.IDNum and ItemID = A.ItemID and SummaryCode = 'PH'
					and Quantity > 0
				Order By Transdate DESC			
			) As SequenceNumber,
			(	Select TOP 1 RefNum
				From INVENTORY..tbInvStockCard 
				Where IDNum = A.IDNum and ItemID = A.ItemID and SummaryCode = 'PH'
					and Quantity > 0
				Order By Transdate DESC			
			) As ChargeSlip
		From Station..tbNursePatientMedicines A
		Where A.IDNum = @IDNUm and A.Quantity > 0
		Order By A.ItemName;
		*/
		
	Select Max(Case When IsNull(D.GenericName,'') = '' Then B.ItemName Else D.GenericName End) As Description, 
	   Max(A.Quantity) As Qty, Max(A.DosageID) Dosage, Max(A.TransDate) TransDate, Max(ItemDesc) As ItemDesc
		From (
			Select IDNum, A.Quantity, A.DosageID, A.ItemID, A.RefNUm, Amount, TransDate
							From INVENTORY..tbInvStockCard A
							Where IDNum = @IDNUm and SummaryCode = 'PH'
			Union All
			Select IDNum, A.Quantity, A.DosageID, A.ItemID, A.RefNUm, (Amount) * -1, TransDate
							From INVENTORY..tbInvStockCard A
							Where IDNum = @IDNUm and SummaryCode = 'PC'
		) A
		Left Outer Join Inventory..tbInvMaster B On A.ItemID = B.ItemID
		Left Outer Join Inventory..tbInvClassification C On B.ItemClassificationID = C.ItemClassificationID
		Left Outer Join Inventory..tbForGeneric D On B.GenericID = D.GenericID
		Where A.IDNum = @IDNUm --And Amount > 0 And ClassificationType = 'D'
		Group By A.ItemID, RefNum				

	END






GO



USE [Medicare]
GO

/****** Object:  StoredProcedure [dbo].[sp_Medic_GetForm3_Discharge]    Script Date: 12/01/2013 03:18:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO











CREATE PROCEDURE [dbo].[sp_Medic_GetForm3_Discharge]
	@IDNum As Varchar(10)
AS
--set @IDNum = '548423'

BEGIN
			DECLARE	@HospitalName As Varchar(50),
					@Accreditation As Varchar(20),
					@Street As Varchar(30),
					@Barangay As Varchar(30),
					@Municipality As Varchar(30),
					@Province As Varchar(30),
					@ZipCode As Varchar(4)
					
			Select	@HospitalName=HospitalName,
				@Accreditation=AccrNum,
				@Street=Street,
				@Barangay=Barangay,
				@Municipality=Municipality,
				@Province=Province,
				@ZipCode=ZipCode
			
			From MEDICARE..tbMedHospital;

		Select top 1 Rtrim(Isnull(M.Lastname,'')) LastName,
			Rtrim(Isnull(M.Firstname,'')) Firstname,
			Rtrim(Isnull(M.Middlename,'')) Middlename,
			P.ADMDate,
			P.DCRDate,
			Patient_Data.dbo.Compute_Age(M.BirthDate,P.ADMDate) Age,
			M.Sex,
			Ph.ADMdiagnosis,
			Ph.ChiefComplaint ,
			Ph.ADMImpression,
			IsNull(Ph.FinalDiagnosis, '') As FinalDiagnosis,
			ISNull(Ph.Procedures, '') As Procedures,
			IsNull(Ph.CourseWard, '') As CourseWard,
			NP.BriefHistory,
			NP.PEskin,
			NP.PEheadneck,
			NP.PEthorax,
			NP.PEabdomen,
			NP.PEextremeties,
			NP.PElympnodes,
			NP.PEgenitalia,
			NP.PErectal,
			NP.PEneurological,
			NV.BP,
			NV.Temperature,
			NV.PulseRate,
			NV.RespRate,
			Case Isnull(P.ResultID,'')
				When '1' then 'Recovered'
				When '2' then 'Improved'
				When '3' then 'Unimproved'
				When '4' then 'Died'
				When '5' then 'Check-Up'
			End Condition,
		         Case Isnull(P.AttendingDr1,'')--IsNull(D.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = P.AttendingDr1--D.DocCode
			           Group by DoctorID ), '' )
		         End Physician,	
		         Case IsNull(S.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = S.DocCode
			           Group by DoctorID ), '' )
		         End Surgeon,
			Case IsNull(S.DatePerformed,'')
				When '' Then ''
				Else
				    S.DatePerformed
			End Operationdate,
		         Case IsNull(A.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = A.DocCode
			           Group by DoctorID ), '' )
		         End Anesthesiologist,
			Case IsNull(A.DatePerformed,'')
				When '' Then ''
				Else
				    A.DatePerformed
			End Anesthedate,
			IsNull(A.ServicePerformed,'') Anesthesia,
			@HospitalName As Hospital,
			@Accreditation As Accreditation,
			@Street As Street,
			@Barangay As Barangay,
			@Municipality As Municipality,
			@Province As Province,
			@ZipCode As Zipcode,
			P.RoomID As Room,
			Patient_Data.dbo.Fn_GetCompleteName(P.HospNum) As PName,
			 Case IsNull(P.AdmittingDr,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
			                      from Build_File..tbCoDoctor
			                      Where DoctorID = P.AdmittingDr
			           Group by DoctorID ), '' )
			 End AdmittingDr,			
			 Case IsNull(P.AdmittingClerk,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') ) 
			                      from Password..tbPasswordMaster
			                      Where EmployeeID = P.AdmittingClerk
			           Group by EmployeeID ), '' )
			 End AdmittingClerk,
			P.HospNum As HospNum,
			Lower(Ltrim(Rtrim(Cast(IsNull(Ph.AdmDiagnosis,'') As Varchar(5000))))) ADMDiagnosis,
			Ph.CourseWard,
			NP.Others As Remarks,
			NV.O2Sat,
			case 
				when Len(Ltrim(Rtrim(Cast(IsNull(Ph.CourseWard,'') As Varchar(5000))))) <=0 then 0
				else 1
			End As CourseCount,
			case 
				when Len(Ltrim(Rtrim(Cast(IsNull(Ph.Procedures,'') As Varchar(5000))))) <=0 then 0
				else 1
			End As ProceduresCount,
			Lower(Ltrim(Rtrim(Cast(IsNull(Ph.ChiefComplaint,'') As Varchar(5000))))) As ChiefComplaints,
			P.ResultID,
			P.DispositionID,
			AB.Disposition,
			(select Count(IDNum) from Radiology..tbXRResult where idnum = @IDNum) As XRResult,
			(select Count(IDNum) from Radiology..tbCTResult where idnum = @IDNum) As CTResult,
			(select Count(IDNum) from Radiology..tbULResult where idnum = @IDNum) As ULResult,
			(select Count(IDNum) from Radiology..tbMRIResult where idnum = @IDNum) As MRIResult,
			(select Count(IDNum) from Radiology..tbMammoResult where idnum = @IDNum) As MammoResult,
			(select Count(IDNum) from Station..tbNurseHomeCarePlan where idnum = @IDNum) As HomeCarePlan,
			(select Count(IDNum) from Station..tbNurseDischargeInstructions where idnum = @IDNum) As Meds,
			(select Count(IDNum) from Station..tbORMasterFile where idnum = @IDNum) As Operative,
			case 
				when Len(Ltrim(Rtrim(Cast(IsNull(Ph.FinalDiagnosis,'') As Varchar(5000))))) <=0 then 0
				else 1
			End As FinalDiagnosisCount
		From PATIENT_DATA..tbPatientHistory Ph
			Inner Join PATIENT_DATA..tbPatient P
				On Ph.IDNum = P.IDNum
			Inner Join PATIENT_DATA..tbMaster M
				On P.Hospnum = M.Hospnum
			Left Outer Join Station..tbNurseProfile NP
				On Ph.IDNum = NP.IDNum
			Left Outer Join Station..tbNurseVitalSigns NV
				On Ph.IDNum = NV.IDNum
			Left Outer Join Medicare..tbMedDoctors D
				On Ph.IDNum = D.IDNum and D.DocType ='P'
			Left Outer Join Medicare..tbMedDoctors S
				On Ph.IDNum = S.IDNum and S.DocType ='S'
			Left Outer Join Medicare..tbMedDoctors A
				On Ph.IDNum = A.IDNum and A.DocType ='A'
			Left Outer Join Build_File..tbCoDisposition AB
				On P.DispositionID = AB.DispositionID
			Where Ph.IDNum = @IDNum;--'503886';
	END











GO



USE [Station]
GO

/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular1]    Script Date: 12/01/2013 02:57:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create PROCEDURE [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular1]
--declare
	@Hospnum		as Varchar(10)
AS

--set @Hospnum = '7623'


select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'H'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(
select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'H'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc
)

UNION ALL

select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'A'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(
select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'A'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc
)











GO



USE [Station]
GO

/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular]    Script Date: 12/01/2013 03:00:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular]
--declare
	@Hospnum		as Varchar(10)
AS

--set @Hospnum = '154358'


select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'H'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(
select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'H'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc
)

UNION ALL

select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'A'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(
select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'A'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc
)









GO



GO

alter table patient_data..tbpatient add [ChaplainsRemark] [text] NULL
go


use build_file
go
----------------------------------------
create table tbCoRoomAmenities
(
RoomID varchar(10),
AmenityID varchar(10),
Status bit ,
Quantity int null
)


create table tbCoAmenities
(
AmenityID varchar(10),
Amenity varchar (50)
)
------------------------------------------
USE [Password]
GO
/****** Object:  Table [dbo].[tbChangeLog]    Script Date: 09/06/2012 03:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbChangeLog](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[UpdateDate] [datetime] NULL,
	[UpdatedBy] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ActionDone] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ModuleName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
-------------------------------------------
USE [Build_File]
GO
/****** Object:  Table [dbo].[tbDBImage]    Script Date: 08/06/2012 13:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbDBImage](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TableName] [varchar](15) NULL,
	[FieldName] [varchar](15) NULL,
	[IDKEY] [varchar](15) NULL,
	[DBImage] [image] NULL,
	[FileType] [varchar](15) NULL,
	[FileSize] [int] NULL,
	[ImgNote] [text] NULL,
	[TransDATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


alter  table [Patient_Data].[dbo].[tbAdmDischarge] alter column  hospnum varchar(8)
GO
alter  table Register.[dbo].[tbUpdateNumber] alter column  hospnum varchar(8)
GO


create table MIntranet.dbo.Messaging (ID bigint IDENTITY (1, 1) NOT NULL,  
                              MessageType integer, 
                              byUserID  varchar(50), 
                              ToUserID  varchar(50), 
                              ToDepartment varchar(50), 
                              MsgBody  text, 
                              MsgDate datetime, 
                              FileData Image, 
                              FileType varchar(15)  
                              )
        GO

      CREATE INDEX [by_Primary] ON MIntranet.dbo.Messaging ([ID])  
        GO

      CREATE INDEX [by_From] ON MIntranet.dbo.Messaging (MessageType,byUserID) 
        GO

      CREATE INDEX [by_To] ON MIntranet.dbo.Messaging (MessageType,ToUserID)  
        GO

      CREATE INDEX [by_Dept] ON MIntranet.dbo.Messaging (MessageType,ToDepartment) 
        GO



USE [patient_data]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbDBImage](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TableName] [varchar](50) NULL,
	[FieldName] [varchar](50) NULL,
	[IDKEY] [varchar](15) NULL,
	[DBImage] [image] NULL,
	[FileType] [varchar](15) NULL,
	[FileName] [varchar](30) NULL,
	[FileSize] [int] NULL,
	[ImgNote] [text] NULL,
	[Confidential] [varchar](1) NULL,
	[TransDATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
create index byTableFieldID on [patient_data].dbo.tbDBImage(TableName,FieldName,IDKEY) 
GO
create index byIDKey on [patient_data].dbo.tbDBImage(IDKEY) 
GO


USE [FixedAsset]
GO
create index byfld_TicketNumber on tblrequester(fld_TicketNumber) 
GO
create index byfld_RequestTo on tblrequester(fld_RequestTo,fld_RepairStatus) 
GO
create index byfld_RequestedBy on tblrequester(fld_RequestedBy) 
GO
create index byfld_RepairStatus on tblrequester(fld_RepairStatus) 
GO
create index byfld_Department on tblrequester(fld_Department) 
GO




USE [Patient_Data]
GO
create index byAdmDate on tbPatient(AdmDate) 
GO




USE [Build_File]
GO
create index byServiceID on tbcoService(ServiceID) 
GO


USE [Build_File]
GO
create index byReligionID on tbcoReligion(ReligionID) 
GO


alter table MIntranet.dbo.Messaging  add MsgStatus Varchar(1)
GO

CREATE INDEX [by_MsgStatus] ON MIntranet.dbo.Messaging (ToUserID,MsgStatus) 
GO


use Patient_Data
GO

ALTER PROCEDURE [dbo].[sp_AOPD_SaveUpdateRights]
@MenuFile bit,
@NewRegistration bit,
@DataSheet bit,
@EditRegistration bit,
@MedicalCertificate bit,
@EditName bit,
@MenuReports bit,
@Indexes bit,
@DailyCensusReport bit,
@ReportOfActivities bit,
@SummaryOfDemography bit,
@MonthlyPhysicianIndex bit,
@OutpatientIndexCard bit,
@MenuOPDTransaction bit,
@MenuResultEntry bit,
@MenuAssessment bit,
@MenuResultViewing bit,
@MenuConsultationInfo bit=null,
@EmployeeID varchar(15)=null,
@ClinicalProcedures bit = 0,
@SendRequest bit = 0,
@NursesActivityRequest bit = 0,
@ViewResults bit = 0,
@VitalSigns bit = 0,
@NursesNotes bit = 0,
@PatientProfile bit = 0,
@ClinicalSummary bit = 0,
@ViewSOA bit = 0,
@MasterFileOnly bit = 0,
@OPDSettings bit = 0
as

if exists(select employeeid from patient_data..tb_AOPD_UserRights where employeeid = @EmployeeID)
   begin
	update patient_data..tb_AOPD_UserRights
		set MenuFile = @MenuFile,
			NewRegistration = @NewRegistration,
			DataSheet = @DataSheet,
			EditRegistration = @EditRegistration,
			MedicalCertificate = @MedicalCertificate,
			EditName = @EditName,
			MenuReports = @MenuReports,
			Indexes = @Indexes,
			DailyCensusReport = @DailyCensusReport,
			ReportOfActivities = @ReportOfActivities,
			SummaryOfDemography = @SummaryOfDemography,
			MonthlyPhysicianIndex = @MonthlyPhysicianIndex,
			OutpatientIndexCard = @OutpatientIndexCard,
			MenuOPDTransaction = @MenuOPDTransaction,
			MenuResultEntry = @MenuResultEntry,
			MenuAssessment = @MenuAssessment,
			EmployeeID = @EmployeeID,	
			MenuresultentryViewing = @MenuResultViewing,
			MenuConsultationInfo = @MenuConsultationInfo,
			ClinicalProcedures = @ClinicalProcedures,
			SendRequest = @SendRequest,
			NursesActivityRequest = @NursesActivityRequest,
			ViewResults = @ViewResults,
			VitalSigns = @VitalSigns,
			NursesNotes = @NursesNotes,
			PatientProfile = @PatientProfile,
			ClinicalSummary = @ClinicalSummary,
			ViewSOA = @ViewSOA,
			MasterFileOnly = @MasterFileOnly,
			OPDSettings = @OPDSettings 
	where employeeid = @Employeeid
   end;
else
   begin
	insert into Patient_data..tb_AOPD_UserRights
		(MenuFile, NewRegistration, DataSheet, EditRegistration, MedicalCertificate, EditName, MenuReports, Indexes, DailyCensusReport,
		 ReportOfActivities,  SummaryOfDemography, MonthlyPhysicianIndex, OutpatientIndexCard, MenuOPDTransaction, MenuResultEntry, 
		 MenuAssessment, EmployeeID, MenuResultEntryViewing, MenuConsultationInfo, ClinicalProcedures, SendRequest, NursesActivityRequest,
		 ViewResults, VitalSigns, NursesNotes, PatientProfile, ClinicalSummary, ViewSOA, MasterFileOnly,OPDsettings)
	Values
		(@MenuFile,  @NewRegistration, @DataSheet, @EditRegistration, @MedicalCertificate, @EditName, @MenuReports, @Indexes, @DailyCensusReport,
		 @ReportOfActivities, @SummaryOfDemography, @MonthlyPhysicianIndex, @OutpatientIndexCard, @MenuOPDTransaction, @MenuResultEntry, 
		 @MenuAssessment, @EmployeeID, @MenuResultViewing, @MenuConsultationInfo, @ClinicalProcedures,@SendRequest, @NursesActivityRequest,
		 @ViewResults, @VitalSigns, @NursesNotes, @PatientProfile, @ClinicalSummary, @ViewSOA, @MasterFileOnly,@OPDSettings)	
   end;
GO

use password
go

alter table tbpasswordmaster add UpdateDate Datetime
GO




USE [Password]
GO

/****** Object:  Table [dbo].[tbPasswordWoms]    Script Date: 09/05/2012 17:14:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbPasswordWoms](
	[EmployeeID] [varchar](50) NULL,
	[Department] [varchar](50) NULL,
	[UserRights] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [Password]
GO
/****** Object:  StoredProcedure [dbo].[sp_Pass_SaveUser]    Script Date: 11/06/2013 18:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Pass_SaveUser] 
@EmployeeID varchar(10),
@LastName varchar(15),
@FirstName varchar(20),
@MiddleName varchar(15),
@Initial varchar(3),
@BirthDate varchar(12),
@Password varchar(32),
@DepartmentID varchar(4),
@OldDepartmentID varchar(4),
@Position varchar(25),
@RightCode1 varchar(1),
@RightCode2 varchar(1)

AS

DECLARE @Name varchar(50) /* for backcompatibility */
declare @cBirth varchar(5)

SET @Name = @LastName + ' ' + @FirstName + ' ' + @MiddleName

IF NOT EXISTS( SELECT EmployeeID FROM tbPasswordMain WHERE EmployeeID = @EmployeeID AND 
	DepartmentID = @DepartmentID)
	INSERT INTO tbPasswordMain
  	(EmployeeID, LastName, FirstName, MiddleName, Initial, BirthDate, Password, DepartmentID,
   	OriginalDepartment, Position, Right_Code1, Right_Code2, ODeptArt, SiteCode)
	VALUES
  	(@EmployeeID, @LastName, @FirstName, @MiddleName, @Initial, @BirthDate, @Password, 
   	@DepartmentID, @OldDepartmentID, @Position, @RightCode1, @RightCode2, '', '')
ELSE
	UPDATE tbPasswordMain
	SET LastName = @LastName,
	FirstName = @FirstName,
	MiddleName = @MiddleName, 
	Initial = @Initial,
	BirthDate = @BirthDate,
	[Password] =  @Password,
	DepartmentID = @DepartmentID,
   	OriginalDepartment = @OldDepartmentID,
	Position =  @Position,
	Right_Code1 =  @RightCode1,
	Right_Code2 = @RightCode2,
	ODeptArt = '', SiteCode = ''
	WHERE EmployeeID = @EmployeeID AND 
	DepartmentID = @DepartmentID
	

/* save to master file -- forward compatability */
if isdate(@BirthDate) = 1
  begin
    if cast(@BirthDate as datetime) <= '01/01/1900'
      begin
        set @cBirth = '00/00'
      end
    else
      begin
        set @cBirth = left(@BirthDate, 5)
      end
  end
else
  begin
    set @cBirth = '00/00'
  end

IF NOT EXISTS( SELECT EmployeeID FROM tbPasswordMaster WHERE EmployeeID = @EmployeeID)
begin
	insert into tbPasswordMaster
  	(EmployeeID, LastName, FirstName, MiddleName, Initial, BirthDate, Password)
	values(@EmployeeID, @LastName, @FirstName, @MiddleName, @Initial, @cBirth, @Password)
end
else
BEGIN
	update tbPasswordMaster
	set lastname= @LastName,
	firstname= @FirstName, 
	middlename= @MiddleName, 
	initial = @Initial, 
	birthdate = @cBirth, 
	password = @Password
	where employeeid = @employeeid
END	
/* 
   update all employee department password
   for consistency
 */
UPDATE tbPasswordMain SET Password = @Password
WHERE EmployeeID = @EmployeeID;

UPDATE tbPasswordMaster SET Password = @Password
WHERE EmployeeID = @EmployeeID;
go