USE Build_FIle
GO


IF COL_LENGTH('TbBuildUserSettings','sInputInsurance') IS NULL
BEGIN
	ALTER TABLE TbBuildUserSettings
	ADD sInputInsurance varchar(1)
END

GO

IF OBJECT_ID('tbCoDoctorInsurance', 'U') IS NULL
BEGIN
  CREATE TABLE tbCoDoctorInsurance (
	doctor_id varchar(50),
	account_num varchar(10),
	date_acquired date NULL,
	date_expiry date NULL
	)	

END

GO

USE Build_FIle
GO

DECLARE @revId AS varchar(10) = (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbCoOtherRevenue' AND COLUMN_NAME = 'OtherRevenueID')

IF @revId = 'varchar'
	ALTER TABLE tbCoOtherRevenue 
	ALTER COLUMN OtherRevenueID varchar(7)
ELSE
	ALTER TABLE tbCoOtherRevenue 
	ALTER COLUMN OtherRevenueID nvarchar(7)
GO

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

Use FixedAsset
Go
ALTER TABLE FixedAsset..tblrequester
ADD
fld_DepHeadDateChanged datetime null,
fld_MMDateChanged datetime null,
fld_CFODateChanged datetime null,
fld_PODateChanged datetime null,
fld_EVPDateChanged datetime null,
fld_DMDateChanged datetime null,
fld_ExecDateChanged datetime null,
fld_AddApp1DateChanged datetime null,
fld_AddApp2DateChanged datetime null,
fld_AddApp3DateChanged datetime null;
Go

USE BUILD_FILE
GO

IF COL_LENGTH('tbCoOtherRevenue','PhicCode') IS NULL
BEGIN
	ALTER TABLE tbCoOtherRevenue
	ADD PhicCode varchar(25)
END
GO

USE Build_FIle
GO

IF COL_LENGTH('tbICDDiagMain','status') IS NULL
BEGIN
	ALTER TABLE tbICDDiagMain
	ADD status bit
END
GO

USE Build_FIle
GO

IF COL_LENGTH('tbCoDoctor','TaxExpireDate') IS NULL
ALTER TABLE tbCoDoctor
ADD TaxExpireDate varchar(20)
GO

USE Build_FIle
GO

IF COL_LENGTH('tbCoDoctor','TaxIssueDate') IS NULL
	ALTER TABLE tbCoDoctor
	ADD TaxIssueDate varchar(20) NULL
GO

USE Build_FIle
GO

IF COL_LENGTH('tbICDDiagMain','active') IS NOT NULL
	EXEC sp_rename 'tbICDDiagMain.active', 'status', 'COLUMN';
GO

IF COL_LENGTH('TbBuildUserSettings','sRevActivation') IS NULL
	ALTER TABLE TbBuildUserSettings
	ADD sRevActivation bit

	ALTER TABLE TbBuildUserSettings
	ALTER COLUMN sInputInsurance bit
GO

alter table patient_data..tbpatientdocs add 	[VerifyDate] [datetime] NULL
GO
alter table patient_data..tbpatientdocs add 	[ID] [bigint] IDENTITY(1,1) NOT NULL
GO

alter table patient_data..tbpatient add 	[ResidentDr1] [varchar](4) NULL
GO
alter table patient_data..tbpatient add 	[ResidentDr2] [varchar](4) NULL
GO
alter table patient_data..tbpatient add 	[ResidentDr3] [varchar](4) NULL
GO
alter table patient_data..tbpatient add 	[ResidentDr4] [varchar](4) NULL
GO

	

alter table doctors..[DOC_Referral] add  [ID] [bigint] IDENTITY(1,1) NOT NULL
GO

USE Build_FIle
GO

IF COL_LENGTH('tbCoDocCategory','status') IS NULL
	ALTER TABLE tbCoDocCategory
	ADD Status bit NOT NULL DEFAULT 1
GO

---------------------------------------------------------------------------------
use Register
go

ALTER TABLE [Register].[dbo].[tbMedicalHistory]
ADD

txtHighBlood VARCHAR(100),
txtRheuFever VARCHAR(100),
txtSTD VARCHAR(100),
txtChronicCough VARCHAR(100),
txtFaint VARCHAR(100),
txtDizziness VARCHAR(100),
txtChestPain VARCHAR(100);
Go

---------------------------------------------------------------------------------
use FixedAsset
go
ALTER TABLE FixedAsset.dbo.tbPR_Items
ALTER COLUMN 
[Description] varchar(500);
---------------------------------------------------------------------------------	

USE Build_FIle
GO

DECLARE @Amount AS varchar(10) = (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbcoadmpackage_items' AND COLUMN_NAME = 'Amount')
IF @Amount = 'float'
	ALTER TABLE tbcoadmpackage_items
	ALTER COLUMN Amount money
GO

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

use Patient_Data
go
ALTER TABLE Patient_Data..tbPatientImage
ALTER COLUMN
[FileName] varchar(50);

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

USE [MEDSMS]
GO
drop index    [byMessageSubTypeTPX] ON [dbo].[SMSLog]
go

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

USE [MEDSMS]
GO
CREATE NONCLUSTERED INDEX [byMessageTypeRecordID] ON [dbo].[SMSLog] 
(
	[MessageTypeTPX] ASC,
	[MessageSubTypeTPX] ASC,
	[RecordID1] ASC,
	[RecordID2] ASC
)WITH (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


use FixedAsset
go
alter table FixedAsset.dbo.tbFAPODetail
add 
[Status] VARCHAR(10),
[RevokedBy]  VARCHAR(100) null,
[DateRevoked]  datetime null;
go
---------------------------------------------------------------------------------

use FixedAsset
go
alter table FixedAsset..tbFAPOHeader
add
fld_TicketNumber int null;
go

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

use FixedAsset
go
alter table FixedAsset.dbo.tblrequester
add
PRFromDepartment varchar(100) null;
go

---------------------------------------------------------------------------------
  use FixedAsset
go
alter table FixedAsset.dbo.tbPR_Items
add 
RevokedBy  VARCHAR(100) null,
DateRevoked  datetime null,
DepartmentID  VARCHAR(10) null;
go


USE [FixedAsset]
GO

/****** Object:  Table [dbo].[tblrequester]    Script Date: 04/26/2016 19:28:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblrequester](
	[fld_TicketNumber] [int] NOT NULL,
	[fld_DateTimeRequest] [varchar](50) NULL,
	[fld_Department] [varchar](50) NULL,
	[fld_RequestNeeded] [varchar](max) NULL,
	[fld_RequestedBy] [varchar](50) NULL,
	[fld_AssignedTo] [varchar](500) NULL,
	[fld_PriorityLevel] [varchar](50) NULL,
	[fld_DateTimeStart] [varchar](50) NULL,
	[fld_DateTimeEnd] [varchar](50) NULL,
	[fld_RepairStatus] [varchar](50) NULL,
	[fld_Cost] [float] NULL,
	[fld_RequestTo] [varchar](50) NULL,
	[fld_BudgetingHead] [varchar](50) NULL,
	[fld_Repairman] [varchar](50) NULL,
	[fld_NotedBy] [varchar](50) NULL,
	[fld_CheckedBy] [varchar](50) NULL,
	[fld_WorkDone] [varchar](max) NULL,
	[fld_RushReason] [varchar](max) NULL,
	[fld_DepHead] [varchar](50) NULL,
	[fld_DepHeadStatus] [varchar](20) NULL,
	[fld_MM] [varchar](50) NULL,
	[fld_MMStatus] [varchar](20) NULL,
	[fld_CFO] [varchar](50) NULL,
	[fld_CFOStatus] [varchar](20) NULL,
	[fld_PO] [varchar](50) NULL,
	[fld_POStatus] [varchar](20) NULL,
	[fld_EVP] [varchar](50) NULL,
	[fld_EVPStatus] [varchar](20) NULL,
	[fld_DM] [varchar](50) NULL,
	[fld_DMStatus] [varchar](20) NULL,
	[fld_ApprovalStatus] [varchar](50) NULL,
	[fld_Exec] [varchar](50) NULL,
	[fld_ExecStatus] [varchar](20) NULL,
	[fld_AddApp1] [varchar](50) NULL,
	[fld_AddApp1Status] [varchar](20) NULL,
	[fld_AddApp2] [varchar](50) NULL,
	[fld_AddApp2Status] [varchar](20) NULL,
	[fld_AddApp3] [varchar](50) NULL,
	[fld_AddApp3Status] [varchar](20) NULL,
	[fld_DepHeadDateChanged] [datetime] NULL,
	[fld_MMDateChanged] [datetime] NULL,
	[fld_CFODateChanged] [datetime] NULL,
	[fld_PODateChanged] [datetime] NULL,
	[fld_EVPDateChanged] [datetime] NULL,
	[fld_DMDateChanged] [datetime] NULL,
	[fld_ExecDateChanged] [datetime] NULL,
	[fld_AddApp1DateChanged] [datetime] NULL,
	[fld_AddApp2DateChanged] [datetime] NULL,
	[fld_AddApp3DateChanged] [datetime] NULL,
	[PRFromDepartment] [varchar](100) NULL,
 CONSTRAINT [PK_tblrequester] PRIMARY KEY CLUSTERED 
(
	[fld_TicketNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO





Use Station
GO

ALTER TABLE Station.dbo.tbNurseNotes ADD Problem text;
GO

Use Doctors
GO

ALTER TABLE Doctors.dbo.Doc_Orders  ADD Problem text;
GO

Use Doctors
GO

ALTER TABLE Doctors.dbo.Doc_Orders ADD NurseNote text;
GO

Use Doctors
GO

ALTER TABLE Doctors.dbo.Doc_Orders ADD NurseID varchar(20);
GO

Use Doctors
GO

ALTER TABLE Doctors.dbo.Doc_Orders ADD TransDateNurseNote datetime;
GO

USE [Station]
GO

CREATE NONCLUSTERED INDEX [iXIDNum_tbNurse_AdministeredMedicines] ON [dbo].[tbNurse_AdministeredMedicines] ([IDNum])
GO