USE [PATIENT_DATA]

GO

alter TABLE [dbo].[tbpatientGuarantor]
alter column    [IDNum] [varchar](15)  NOT NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[GuarantorName] [varchar](100)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[GuarantorAddress] [varchar](200)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[GuarantorEmployer] [varchar](100)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[GuarantorTelNum] [varchar](20)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[EmployerAddress] [varchar](100)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[PatientRelationship] [varchar](50)  NULL

alter TABLE [dbo].[tbpatientGuarantor]
alter column 	[GuarantorRelation] [varchar](50)  NULL

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

alter TABLE [dbo].[tbpatientGuarantor_Revoke]
alter column	[GuarantorName] [varchar](50)  NULL

alter TABLE [dbo].[tbpatientGuarantor_Revoke]
alter column		[GuarantorAddress] [varchar](100)  NULL

alter TABLE [dbo].[tbpatientGuarantor_Revoke]
alter column		[GuarantorEmployer] [varchar](50)  NULL

alter TABLE [dbo].[tbpatientGuarantor_Revoke]
alter column		[EmployerAddress] [varchar](100)  NULL

alter TABLE [dbo].[tbMaster2]
alter column		FatherOccupation [varchar](40)  NULL

alter TABLE [dbo].[tbMaster2]
alter column		MotherOccupation [varchar](40)  NULL

alter TABLE [dbo].[tbMaster2]
alter column		SpouseAddress [varchar](80)  NULL

Go
----------------------------------------------------------------------
alter table Patient_Data..tbMaster2 add
SpousePosition varchar(40),
Employerposition varchar(40)

Go
----------------------------------------------------------------------
alter table Patient_Data..tbPatientInformant add
InformantOccupation varchar(40)

GO
----------------------------------------------------------------------
alter table Build_File..tbCoAdmPackage add
AdmActive bit not null default('1')

GO

----------------------------------------------------------------------
alter TABLE Patient_Data..tbMaster add
AccountNumII varchar(12) 

GO
----------------------------------------------------------------------
USe Patient_Data

GO


create table patient_Data..tbAdmNicuCommunication(
	SequenceNumber int PRIMARY KEY IDENTITY,
	HospNum varchar(15),
	IDNum varchar(15),
	PatientName varchar(50),
	Modification varchar(100),
	DateModified datetime,
	NicuUser varchar(80),
	DateConfirmed datetime,
	ConfirmingUser varchar(80),
	Status varchar(1) 
	)

GO
-----Create copies with different IDNum as much as you need
--insert into tbAdmNicuCommunication(HospNum,IDNum,PatientName,Modification,DateModified,NicuUser)
--values('12345','67891','Cruz, BabyBoy M.','Room Transfer: room NICU1 to room 415',getdate(),'Kristy Anne Aban')

--insert into tbAdmNicuCommunication(HospNum,IDNum,PatientName,Modification,DateModified,NicuUser)
--values('12346','67892','Bruce, BabyBoy A.','Room Transfer: room 234 to room 234',getdate(),'Kristy Anne Aban')


---------------------------------------------------------------------------
USE BUILD_FILE

GO

create table build_file..tbcoRelationship(
	RelationshipID varchar(2),
	Relationship varchar(30),
	RelationshipStatus bit not null default('1')
)
/*

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('1','Mother','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('2','Father','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('3','Son','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('4','Daughter','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('5','Nephew','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('6','Niece','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('7','Brother','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('8','Sister','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('9','Friend','1')

insert into build_file..tbcoRelationship(RelationshipID,Relationship,RelationshipStatus)
values('10','Others','1')
*/


Go

----------------------------------------------------------------------
USE PATIENT_DATA


GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedAdmissionInfo] 
     @IDNum  as varchar(10)
AS

Select * from tbPatient2 where IDNum = @IDNum

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

ALTER PROCEDURE [dbo].[sp_Adm_SearchPackage]
@SearchCriteria varchar(50)

AS
    select isnull(Package,'') as Description, isnull(PackageID,'') as Code
    from Build_File..tbCoAdmPackage
    where  AdmActive ='1' And 
	(Package like @SearchCriteria + '%' or PackageID = @SearchCriteria  or @SearchCriteria = '')
    order by Package

GO


----------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_Adm_UpdateAddedInfo]
@IDNum as varchar(12),
@PackageID as varchar(5),
@AdmissionTypeID as varchar(2),
@UserID as varchar(10) ,
@SeniorCitizen as bit = 0,
@WithAdmissionKit as bit = 0,
@SeniorCitizenID as varchar(20)= Null,
@isOutborn as varchar(1) = null,
@isWellBaby as varchar(1) = null,
@MotherIDNum as varchar(15) = Null,
@WithTransferFee as bit = 0,
@isCaesarianDelivery as varchar(1) = null,
@NoticeOfAdmID as varchar(1) =null,
@CellNumber as varchar(12)=null,
@EmailAdd as varchar(40),
@SpouseEmp as varchar(30),
@GuarantorRelation as varchar(30),
@GSISNum as varchar(20),
@SSSNum as varchar(20),
@HospPlan as varchar(1),

@SpousePosition as varchar(40) = null,
@SpouseAddress as varchar(80)= null,
@SpouseTelNum as varchar(15)= null,
@FatherOccupation as varchar(40) = null,
@MotherOccupation as varchar(40) = null,
@Employerposition as  varchar(40)= null,
@InformantOccupation as varchar(40)= null,
@GuarantorPosition as varchar(40)= null,
@AccountNumII as varchar(15)= null


AS
Declare @Hospnum as varchar(15)

Update tbPatient2
Set PackageID = @PackageID,
AdmissionTypeID = @AdmissionTypeID,
SeniorCitizen = @SeniorCitizen,
NoticeOfAdmID = @NoticeOfAdmID
Where IDNum = @IDNum;

Update tbPatient
Set UserID = @UserID,
WithAdmissionKit = @WithAdmissionKit,
isOutborn = case when @isOutborn = '' then null else @isOutborn end,
isWellBaby = case when @isWellBaby = '' then null else @isWellBaby end,
MotherIDNum = @MotherIDNum,
WithTransferFee = @WithTransferFee,
isCaesarianDelivery = case when @isCaesarianDelivery = '' then null else @isCaesarianDelivery end
Where IDNum = @IDNum;

set @Hospnum = (Select Hospnum from tbPatient where IDNum = @IDNum);

Update tbMaster
Set SeniorCitizen = @SeniorCitizen,
SeniorCitizenID = @SeniorCitizenID,
Cellnum = @CellNumber,
EmailAddress = @EmailAdd,
SpouseEmployer = @SpouseEmp,
GSISNum = @GSISNum,
SSSNum = @SSSNum,
HospPlan = @HospPlan,
AccountNumII = @AccountNumII
Where Hospnum = @Hospnum;

Update tbPatientGuarantor
set GuarantorRelation = @GuarantorRelation,
	GuarantorOccupation = @GuarantorPosition
where IDNum = @IDNum;


Update tbMaster2
set SpousePosition = @SpousePosition,
	SpouseAddress = @SpouseAddress,
	SpouseTelNum = @SpouseTelNum,
	FatherOccupation = @FatherOccupation,
	MotherOccupation  = @MotherOccupation,
	Employerposition = @EmployerPosition
Where Hospnum = @Hospnum;

Update tbPatientInformant
set InformantOccupation = @InformantOccupation
where IDNum = @IDNum;

GO

------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedInfo]
@IDNum as varchar(10)
AS
Select P2.PackageID,
P2.AdmissionTypeID,
P.Package,
A.AdmissionType,
P2.SeniorCitizen,
M.Title,
P2.NoticeOfAdmID,
N.NoticeOfAdm,
M.Cellnum,
M.EmailAddress,
M.SpouseEmployer,
G.GuarantorRelation,
M.GSISNum,
M.SSSNum,
M.HospPlan,

M2.SpousePosition,
M2.SpouseAddress,
M2.SpouseTelNum,
M2.FatherOccupation,
M2.MotherOccupation,
M2.EmployerPosition,
G.GuarantorOccupation,
I.InformantOccupation,
C.AccountNum,
C.Company


From Patient_Data..tbPatient2 P2
Left Outer Join Build_File..tbCoAdmPackage P on P2.PackageID = P.PackageID
Left Outer Join Build_File..tbCoAdmissionType A on A.AdmissionTypeID = p2.AdmissionTypeID
Left Outer Join Patient_Data..tbPatient P1 on P2.IdNum = P1.IDNum
left Outer Join Patient_Data..tbMaster M on P1.HospNum = M.HospNum
left Outer Join Build_File..tbCoNoticeOfAdm N on P2.NoticeOfAdmID = N.NoticeOfAdmID
left Outer Join Patient_Data..tbPatientGuarantor G on P1.IdNum = G.IdNum
left Outer Join Patient_Data..tbMaster2 M2 on P1.HospNum = M2.HospNum
Left Outer Join Patient_Data..tbPatientInformant I on P1.Idnum = I.IdNum
Left Outer Join Build_file..tbcoCompany C on M.AccountNumII = C.AccountNum

Where P2.IDNum = @IDNum;

GO




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

Create Procedure Check_Nicu_Admitting_Communication 
AS
Select '',
	HospNum,
	IDNum,
	PatientName,
	Modification,
	left(DateModified,11) as DateModified,
	NicuUser,
	SequenceNumber
From tbAdmNicuCommunication
Where  status is null or status =''

GO