USE [Patient_Data]
GO
/****** Object:  Table [dbo].[tbPatientDocs]    Script Date: 07/10/2014 18:04:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbPatientDocs](
	[ConversionTime] [datetime] NULL,
	[HealthRecordID] [varchar](50) NULL,
	[HospitalNumber] [varchar](50) NULL,
	[HospitalName] [varchar](50) NULL,
	[RequestNum] [varchar](50) NULL,
	[LabSection] [varchar](50) NULL,
	[LabExam] [varchar](50) NULL,
	[DocType] [varchar](50) NULL,
	[FileName] [varchar](max) NULL,
	[FileData] [varbinary](max) NULL,
	[FileType] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [byDate] ON [dbo].[tbPatientDocs] 
(
	[ConversionTime] 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
CREATE NONCLUSTERED INDEX [byHealthRecID] ON [dbo].[tbPatientDocs] 
(
	[HealthRecordID] 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
CREATE NONCLUSTERED INDEX [byHospNum] ON [dbo].[tbPatientDocs] 
(
	[HospitalNumber] 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
/****** Object:  Table [dbo].[tbmaster2]    Script Date: 07/10/2014 18:04:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbmaster2](
	[HospNum] [varchar](10) NOT NULL,
	[BirthPlace] [varchar](50) NULL,
	[NationalityID] [varchar](2) NULL,
	[ReligionID] [varchar](2) NULL,
	[Spouse] [varchar](50) NULL,
	[SpouseAddress] [varchar](50) NULL,
	[SpouseTelNum] [varchar](15) NULL,
	[Father] [varchar](50) NULL,
	[FatherAddress] [varchar](50) NULL,
	[FatherTelNum] [varchar](15) NULL,
	[Mother] [varchar](50) NULL,
	[MotherAddress] [varchar](50) NULL,
	[MotherTelNum] [varchar](15) NULL,
	[Employer] [varchar](50) NULL,
	[EmployerAddress] [varchar](50) NULL,
	[EmployerTelNum] [varchar](15) NULL,
	[ContactName] [varchar](50) NULL,
	[ContactAddress] [varchar](50) NULL,
	[ContactTelNum] [varchar](15) NULL,
	[ContactRelation] [varchar](50) NULL,
	[BarangayCaptain] [varchar](50) NULL,
	[NearestKin] [varchar](50) NULL,
	[KinAddress] [varchar](50) NULL,
	[KinRelation] [varchar](30) NULL,
	[KinTelNum] [varchar](30) NULL,
	[POSITION] [varchar](50) NULL,
	[EmploymentType] [int] NULL,
	[EmploymentLocation] [varchar](50) NULL,
	[EmploymentFax] [varchar](15) NULL,
	[EmployerEmail] [varchar](50) NULL,
	[SpouseEmpName] [varchar](50) NULL,
	[SpouseEmpAddress] [varchar](50) NULL,
	[SpouseEmpTelNum] [varchar](15) NULL,
	[SpouseEmpFax] [varchar](15) NULL,
	[SpouseEmpEmail] [varchar](50) NULL,
	[PhilHealth] [varchar](1) NULL,
	[Insurance] [varchar](50) NULL,
	[Household] [varchar](10) NULL,
	[EmployerFax] [varchar](15) NULL,
	[CompanyID] [varchar](10) NULL,
	[UnionMember] [varchar](1) NULL,
	[VesselID] [varchar](8) NULL,
	[Rating] [varchar](20) NULL,
	[DateMarried] [smalldatetime] NULL,
	[SpouseMaidenName] [varchar](30) NULL,
	[SpouseCellNum] [varchar](15) NULL,
	[SpouseSSSNum] [varchar](15) NULL,
	[SpousePHICNum] [varchar](15) NULL,
	[SpouseEmail] [varchar](15) NULL,
	[SpouseOccupation] [varchar](30) NULL,
	[Education] [smallint] NULL,
	[ReferredBy] [varchar](50) NULL,
	[SpouseMedsysHealthRecID] [varchar](50) NULL,
	[MotherMedsysHealthRecID] [varchar](50) NULL,
	[FatherMedsysHealthRecID] [varchar](50) NULL,
	[ReferredByMedsysHealthRecID] [varchar](50) NULL,
	[GuarantorMedsysHealthRecID] [varchar](50) NULL,
	[ContactMedsysHealthRecID] [varchar](50) NULL,
	[KinMedsysHealthRecID] [varchar](50) NULL,
	[SpousePosition] [varchar](40) NULL,
	[Employerposition] [varchar](40) NULL,
	[PHICNum] [varchar](50) NULL,
	[FatherOccupation] [varchar](50) NULL,
	[MotherOccupation] [varchar](50) NULL,
	[OfficeNum] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [tbmaster24] ON [dbo].[tbmaster2] 
(
	[HospNum] 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, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tbmaster]    Script Date: 07/10/2014 18:04:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbmaster](
	[HospNum] [varchar](10) NOT NULL,
	[LastName] [varchar](30) NOT NULL,
	[FirstName] [varchar](30) NOT NULL,
	[MiddleName] [varchar](30) NOT NULL,
	[AccountNum] [varchar](8) NULL,
	[HouseStreet] [varchar](100) NULL,
	[Barangay] [varchar](50) NULL,
	[ZipCode] [varchar](4) NULL,
	[Sex] [varchar](1) NULL,
	[BirthDate] [datetime] NULL,
	[CivilStatus] [varchar](1) NULL,
	[Occupation] [varchar](30) NULL,
	[Age] [varchar](10) NULL,
	[TelNum] [varchar](15) NULL,
	[PatientStatus] [varchar](1) NULL,
	[OPDNum] [varchar](8) NULL,
	[XRayNum] [varchar](10) NULL,
	[UltraNum] [varchar](10) NULL,
	[SSSGSISNum] [varchar](15) NULL,
	[CTNum] [varchar](10) NULL,
	[BloodType] [varchar](3) NULL,
	[SeniorCitizen] [bit] NULL,
	[FileNum] [varchar](10) NULL,
	[ControlNum] [int] NULL,
	[Title] [varchar](30) NULL,
	[PrepaidNum] [varchar](10) NULL,
	[SeqNum] [varchar](8) NULL,
	[SeniorCitizenWithID] [bit] NULL,
	[TemporaryAddress] [varchar](70) NULL,
	[Citizenship] [varchar](30) NULL,
	[MSSClassification] [varchar](2) NULL,
	[MSSDiscountValidity] [datetime] NULL,
	[MSSDiscountExpiry] [datetime] NULL,
	[MSSNum] [varchar](15) NULL,
	[GovDiscountExpiry] [datetime] NULL,
	[SeniorCitizenID] [varchar](20) NULL,
	[SCBookletNumber] [varchar](20) NULL,
	[MRINum] [varchar](10) NULL,
	[CardNumber] [varchar](50) NULL,
	[Cellnum] [varchar](30) NULL,
	[Email] [varchar](50) NULL,
	[MammoNum] [varchar](10) NULL,
	[EmpNum] [varchar](20) NULL,
	[CDHHActive] [bit] NULL,
	[CDHHStatus] [varchar](2) NULL,
	[CDHAccountNum] [varchar](15) NULL,
	[CDHRelation] [varchar](2) NULL,
	[PatientType] [varchar](1) NULL,
	[OBNum] [varchar](10) NULL,
	[isBDayEstimated] [varchar](50) NULL,
	[MedsysHealthRecID] [varchar](50) NULL,
	[UserID] [varchar](50) NULL,
	[Password] [varchar](50) NULL,
	[VerifiedBy] [varchar](30) NULL,
	[VerifiedByDate] [datetime] NULL,
	[CellNum3] [varchar](20) NULL,
	[CellNum2] [varchar](20) NULL,
	[CityMunicipalityPSGC] [varchar](10) NULL,
	[Country] [varchar](50) NULL,
	[CountryTemp] [varchar](50) NULL,
	[CityMunicipalityPSGCTemp] [varchar](10) NULL,
	[BarangayCaptainTemp] [varchar](50) NULL,
	[HouseStreetTemp] [varchar](100) NULL,
	[BarangayTemp] [varchar](50) NULL,
	[ZipCodeTemp] [varchar](4) NULL,
	[TelNumTemp] [varchar](50) NULL,
	[EmailAddress] [varchar](20) NULL,
	[SpouseEmployer] [varchar](30) NULL,
	[GSISNum] [varchar](20) NULL,
	[SSSNum] [varchar](20) NULL,
	[HospPlan] [varchar](1) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tbmaster] ADD [NucNum] [varchar](20) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[tbmaster] ADD [OP_filenum] [varchar](15) NULL
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [tbmaster2] ON [dbo].[tbmaster] 
(
	[HospNum] 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, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_tbmaster_22_13959126__K2_K4_K3_K1] ON [dbo].[tbmaster] 
(
	[LastName] ASC,
	[MiddleName] ASC,
	[FirstName] 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
CREATE NONCLUSTERED INDEX [_dta_index_tbmaster_22_13959126__K4_K1_K2_K3_5_10] ON [dbo].[tbmaster] 
(
	[MiddleName] ASC,
	[LastName] ASC,
	[FirstName] ASC
)
INCLUDE ( [AccountNum],
[BirthDate]) 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
CREATE NONCLUSTERED INDEX [BirthDate] ON [dbo].[tbmaster] 
(
	[BirthDate] 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  = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [by_MedsysHealthRecID] ON [dbo].[tbmaster] 
(
	[MedsysHealthRecID] 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
CREATE NONCLUSTERED INDEX [byCardNumber] ON [dbo].[tbmaster] 
(
	[CardNumber] 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
CREATE NONCLUSTERED INDEX [LastNameSexBirthDate] ON [dbo].[tbmaster] 
(
	[LastName] ASC,
	[Sex] ASC,
	[BirthDate] 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  = OFF) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tbDBImage]    Script Date: 07/10/2014 18:04:50 ******/
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](50) 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 NONCLUSTERED INDEX [byIDKey] ON [dbo].[tbDBImage] 
(
	[IDKEY] 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
CREATE NONCLUSTERED INDEX [byTableFieldID] ON [dbo].[tbDBImage] 
(
	[TableName] ASC,
	[FieldName] ASC,
	[IDKEY] 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
/****** Object:  Default [DF__tbmaster__CDHHAc__6498B3DB]    Script Date: 07/10/2014 18:04:50 ******/
ALTER TABLE [dbo].[tbmaster] ADD  DEFAULT ((0)) FOR [CDHHActive]
GO



USE [Radiology]
GO

ALTER TABLE [dbo].[tbRISPatientQueueNo]
ADD [ConvertStatus] [varchar](1) NULL
GO






USE [Patient_Data]
GO
/****** Object:  Table [dbo].[tbPortalLog]    Script Date: 12/14/2015 14:23:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbPortalLog](
	[MedsysHealthRecID] [varchar](max) NULL,
	[Hospnum] [varchar](max) NULL,
	[PatientName] [varchar](max) NULL,
	[LogInTime] [datetime] NULL,
	[LogOutTime] [datetime] NULL,
	[TimesVisited] [int] NOT NULL CONSTRAINT [DF_tbPortalLog_TimesVisited]  DEFAULT ((0))
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF