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)  NULL,
	[ActionDone] [text]  NULL,
	[ModuleName] [varchar](30)  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 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



        alter table doctors.dbo.vitalsigns alter column HospNum varchar(50) null 
        GO

        alter table doctors.dbo.vitalsigns alter column DoctorID varchar(50) null 
        GO

        alter table doctors.dbo.vitalsigns alter column Temp varchar(10) null 
        GO

        alter table doctors.dbo.vitalsigns alter column PulseRate varchar(10) null 
        GO

        alter table doctors.dbo.vitalsigns alter column RespRate varchar(10) null 
        GO

        alter table doctors.dbo.vitalsigns alter column Weight varchar(10) null 
        GO

        alter table doctors.dbo.vitalsigns alter column HeartRate  varchar(10) null 
        GO

     
        alter table doctors.dbo.appointments add  ID bigint IDENTITY (1, 1) NOT NULL 
        GO


        alter table doctors.dbo.appointments add   MedsysHealthRecID varchar(50) null 
        GO


        alter table doctors.dbo.appointments alter column DoctorID varchar(50) null 
        GO
 
 

        alter table patient_data.dbo.tbmaster add   MedsysHealthRecID varchar(50)
	GO
 

        alter table patient_data.dbo.tbmaster add   UserID  varchar(50)
	GO
 
        alter table patient_data.dbo.tbmaster add  Password  varchar(50)
	GO
   
        alter table build_file.dbo.tbCoDoctor alter column DoctorID varchar(50) null 
   	GO

        alter table patient_data.dbo.tbOutPatient alter column DoctorID1 varchar(50) null 
        GO
        alter table patient_data.dbo.tbOutPatient alter column DoctorID2 varchar(50) null 
        GO
        alter table patient_data.dbo.tbOutPatient alter column DoctorID3 varchar(50) null 
        GO
        alter table patient_data.dbo.tbOutPatient alter column DoctorID4 varchar(50) null 
        GO
        alter table patient_data.dbo.tbOutPatient alter column DoctorID5 varchar(50) null 
        GO

        alter table password.dbo.tbPasswordMain alter column SiteCode varchar(50) null 
        GO
     
        alter table register.dbo.tbPhysicalExamination add   POEALicense varchar(50) null 
        GO


        alter table register.dbo.tbPhysicalExamination add   WorkType varchar(1) null 
        GO
 

        CREATE INDEX [by_MedsysHealthRecID] ON Patient_data.[dbo].[tbmaster] ([MedsysHealthRecID]) 
        GO
 

        Select   * Into patient_data.dbo.tbMasterHist From  patient_data.dbo.tbmaster  WHERE 1=0
        GO

        Select   * Into patient_data.dbo.tbMaster2Hist From  patient_data.dbo.tbmaster2 WHERE 1=0 
        GO


        alter table patient_data.dbo.tbMasterHist add ID bigint IDENTITY (1, 1) NOT NULL 
        GO

        alter table patient_data.dbo.tbMasterHist add Ver integer null  
        GO

        alter table patient_data.dbo.tbMasterHist add Action VarChar(10) null 
        GO

        alter table patient_data.dbo.tbMasterHist add ActionBy VarChar(30) null  
        GO

        alter table patient_data.dbo.tbMasterHist add ActionDate DateTime null  
        GO

        alter table patient_data.dbo.tbMaster2Hist add Ver integer null  
        GO
  
        alter table doctors.dbo.Doc_Orders alter column DoctorID varchar(50) null 
        GO 

        alter table patient_data.dbo.tbmaster add VerifiedBy varchar(30) 
        GO
        alter table patient_data.dbo.tbmasterhist add VerifiedBy varchar(30)
        GO

        alter table patient_data.dbo.tbmaster add VerifiedByDate datetime 
        GO
        alter table patient_data.dbo.tbmasterhist add VerifiedByDate datetime 
        GO


        alter table patient_data.dbo.tbmaster2 add Education smallint NULL
        GO
        alter table patient_data.dbo.tbmaster2hist add Education smallint NULL
        GO


        alter table patient_data.dbo.tbmaster add CellNum3 [varchar](20) NULL
        GO
        alter table patient_data.dbo.tbmasterhist add CellNum3[varchar](20) NULL
        GO


        alter table patient_data.dbo.tbmaster add CellNum2 [varchar](20) NULL
        GO
        alter table patient_data.dbo.tbmasterhist add CellNum2 [varchar](20) NULL
        GO

 
        alter table patient_data.dbo.tbmaster add  CityMunicipalityPSGC [varchar](10) NULL
        GO
        alter table patient_data.dbo.tbmasterhist add  CityMunicipalityPSGC [varchar](10) NULL
        GO

 
        alter table patient_data.dbo.tbmaster add  Country [varchar](50) NULL
        GO
        alter table patient_data.dbo.tbmaster add  CountryTemp [varchar](50) NULL
        GO

        alter table patient_data.dbo.tbmasterhist add  Country [varchar](50) NULL
        GO
        alter table patient_data.dbo.tbmasterhist add  CountryTemp [varchar](50) NULL
        GO



        alter table patient_data.dbo.tbmasterhist add  CityMunicipalityPSGCTemp [varchar](10) NULL
        GO
        alter table patient_data.dbo.tbmasterhist add  BarangayCaptainTemp [varchar](50) NULL
        GO

        alter table patient_data.dbo.tbmaster add  CityMunicipalityPSGCTemp [varchar](10) NULL
        GO
        alter table patient_data.dbo.tbmaster add  BarangayCaptainTemp [varchar](50) NULL
        GO
    
 

        alter table patient_data.dbo.tbmaster2  alter column SpouseEmail varchar(50) null 
        GO

        alter table patient_data.dbo.tbmaster2Hist  alter column SpouseEmail varchar(50) null 
        GO

        alter table patient_data.dbo.tbmaster2Hist  alter column SpouseAddress varchar(250) null 
        GO

        alter table patient_data.dbo.tbmaster2Hist  alter column FatherAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2Hist  alter column MotherAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2Hist  alter column EmployerAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2Hist  alter column ContactAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2Hist  alter column KinAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2Hist  alter column SpouseEmpAddress varchar(250) null 
        GO


        alter table patient_data.dbo.tbmaster2  alter column SpouseAddress varchar(250) null 
        GO

        alter table patient_data.dbo.tbmaster2  alter column FatherAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2  alter column MotherAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2  alter column EmployerAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2  alter column ContactAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2  alter column KinAddress varchar(250) null 
        GO
        alter table patient_data.dbo.tbmaster2  alter column SpouseEmpAddress varchar(250) null 
        GO

     

        alter table patient_data.dbo.tbOutPatient add ReferralSource varchar(50) 
        GO
        alter table patient_data.dbo.tbOutPatient add ReferralBy varchar(50)
        GO

     
        alter table patient_data.dbo.tbOutPatient add isPHIC bit   NULL
        GO

    
 
        alter table patient_data.dbo.tbOutPatient add isFollowUp bit NULL
        GO
     
        alter table doctors.dbo.Doc_Follow_UP alter column DoctorID varchar(50)
        GO
 

        create table Doctors.dbo.Diagnosis_Simple (ID bigint IDENTITY (1, 1) NOT NULL,  
                      [MedsysHealthRecID] [varchar](50) NULL, 
                      [HospNum] [varchar](8)  NULL, 
                      [IdNum] [varchar](8)  NULL, 
                      [DoctorID] [varchar](50)  NULL, 
                      [PatientType] [varchar](1) NULL, 
                      [ConsDate] [datetime]  NULL, 
                      [DiagnosesID]  [varchar](10)  NULL, 
                      [Sequence] Integer  NULL 
                        )


        GO


        create table Build_File.dbo.tbDiagSimple  (ID bigint IDENTITY (1, 1) NOT NULL,  
                                      Code varchar(10), 
                                      Description  varchar(50), 
                                        )

        GO



     

        create table Build_File.dbo.tbCoOccupation (ID bigint IDENTITY (1, 1) NOT NULL,  
                      [Code] [varchar](10) NULL, 
                      [Description] [varchar](50)  NULL, 
                      [Status] [varchar](1)  NULL 
                        )

        GO



 


        alter table patient_data.dbo.tbmaster add  HouseStreetTemp  [varchar](100) NULL
        GO
        alter table patient_data.dbo.tbmaster add  BarangayTemp [varchar](50) NULL
        GO
        alter table patient_data.dbo.tbmaster add  ZipCodeTemp [varchar](4) NULL
        GO
        alter table patient_data.dbo.tbmaster add  TelNumTemp [varchar](50) NULL
        GO

        alter table patient_data.dbo.tbmaster2 add  ReferredBy varchar(50)
        GO

        alter table patient_data.dbo.tbmaster2 add  SpouseMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  MotherMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  FatherMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  ReferredByMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  GuarantorMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  ContactMedsysHealthRecID varchar(50)
        GO
        alter table patient_data.dbo.tbmaster2 add  KinMedsysHealthRecID varchar(50)
        GO

     
        alter table doctors.dbo.Doc_Notes add Conf VarChar (1)  
        GO
 
     
        create table Doctors.dbo.tbPatientMeds (ID bigint IDENTITY (1, 1) NOT NULL,  
                      CompanyID varchar(10), 
                      Department  varchar(15), 
                      HospNum varchar(10), 
                      IDNum varchar(10), 
                      TransDATE datetime, 
                      ItemID varchar(10), 
                      ItemDesc varchar(50), 
                      Quantity float, 
                      Dosage varchar(8), 
                      Remarks text, 
                      RevenueID varchar(2), 
                      DoctorID varchar(15), 
                      UserID varchar(10), 
                      [Status] [varchar](1)  NULL 
                        )

        GO
    
 
        create table Build_File.dbo.tbMedicalConditions (ID bigint IDENTITY (1, 1) NOT NULL,  
                      ItemID varchar(10), 
                      ItemDesc varchar(50), 
                      Remarks text, 
                      Gender varchar(2), 
                      Part  varchar(15), 
                      Category varchar(10), 
                      [Status] [varchar](1)  NULL 
                        )

        GO
    
 

        alter table patient_data.dbo.tbcashpatient add  RegisteredDate datetime NULL
        GO
    
 
        create table Build_File.dbo.eMailRegister (ID bigint IDENTITY (1, 1) NOT NULL,  
                      Name varchar(10), 
                      eMailAddress varchar(50), 
                      [EStatus] [varchar](1)  NULL 
                        )

        GO
    
 
        alter table patient_data.dbo.tbhospitalinfo add [LogoImage] [image] NULL 
        GO
     
        alter table password.dbo.tbpasswordmaster add [LogoImage] [image] NULL 
        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