use patient_data
go


-------------------------------------------------------------
alter table patient_data..tbmaster2
add PHICNum varchar(50) null
GO

-------------------------------------------------------------
USE [Patient_Data]
GO

/****** Object:  StoredProcedure [dbo].[sp_AOPD_Load_TbMaster]    Script Date: 05/21/2012 12:55:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_AOPD_Load_TbMaster')
EXEC ('CREATE PROC dbo.sp_sp_AOPD_Load_TbMaster AS SELECT 1')
GO





ALTER PROCEDURE [dbo].[sp_AOPD_Load_TbMaster]
   @Hospnum as varchar(10)    
AS

  Select m.hospnum, 
	isnull(m.lastname,'') as Lastname, 
	isnull(m.firstname,'') as FirstName,  
	isnull(m.middlename,'') as MiddleName, 
	isnull(m.housestreet,'')as HouseStreet, 
	isnull(m.barangay,'') as Barangay, 
	m.Zipcode,
	(case when isnull(c.town,'') + ',  ' + isnull(c.province,'') = ', ' then  '' 
	         else isnull(c.town,'') + ',  ' + isnull(c.province,'') End) as province, 
	isnull(m.telnum,'') as TelNum, 
	m.birthdate as BirthDate, 
	isnull(m.sex,'') as Sex, 
	isnull(m.age,0) as Age,
	isnull(m2.Employer,'') as Employer,
	isnull(m2.EmployerAddress,'') as EmployerAddress, 
	isNull(m2.EmployerTelNum,'') as EmployerTelNum,
        M.CivilStatus , isnull(convert(varchar(10),m.GovDiscountExpiry,101),'') as GovDiscountExpiry,	
	isNull(M.BloodType,'') [BloodType] ,
	isNull(M2.BirthPlace,'') [BirthPlace], 
	isNull(M.Occupation,'') [Occupation] ,
	isNull(N.Nationality,'') [Nationality] ,
	isNull(R.Religion,'') [Religion], 	
	isnull(M2.NationalityID,'') AS NationalityID,
        isnull(M2.ReligionID,'') AS ReligionID,
        isnull(M.Title,'') AS Title, SSSGSISNum, m2.phicnum
	

    from tbmaster m left outer join Build_File..tbcoaddress c
	     on m.zipcode = c.zipcode
	left outer join tbMaster2 m2 on m.hospNum=m2.HospNum
	left outer join tbPatient P on m.HospNum=p.HospNum
	left outer join build_file..tbcoNationality N on N.NationalityID = M2.NationalityID
	left outer join build_file..tbcoReligion R on R.ReligionID = M2.ReligionID
    where m.hospnum = @HospNum


insert into patient_data..tbopdmenu (MenuID,Menu,Status,MenuGroup,MainMenu)values('81','User Logs','1','A','2')

alter table Patient_data..tbHospitalInfo
add OtherInfo text

insert into patient_data..tbopdmenu (MenuID,Menu,Status,MenuGroup,MainMenu)values('82','User Validation','1','A','2')


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


alter table Patient_data..tbopd_settings
add MarkUp bit
go


-------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'OPD_SaveOPDSettings')
EXEC ('CREATE PROC dbo.OPD_SaveOPDSettings AS SELECT 1')
GO


ALTER Procedure [dbo].[OPD_SaveOPDSettings]

@isAllowNewOPSTatRate varchar(1),
@isAllowHolidayRate varchar(1),
@isAllowAfterFivePMRate varchar(1),
@isAllowWeekendRate varchar(1),
@isAllowAfterFiveLabChemOnly varchar(1),
@isAllowAfterFiveAllRevenues varchar(1),
@OPDStatRate float,
@HolidayRate float,
@AfterFivePmRate float,
@WeekendRate float,
@MaxMarkUpRate float,
@StatAndAfterFivePMRate float,
@StatAndWeekendRate float,
@HolidayAndWeekend float,
@HolidayAndAfterFivePMRate float,
@WeekendAfterFive float,
@dtWeekendStartMarkUp smalldatetime,
@dtWeekendEndMarkUp smalldatetime,
@isAssessmentMarkUp as varchar(1),
@isPostChargeMarkup as varchar(1),
@dtWeekendStartMarkUp2 smalldatetime,
@dtWeekendEndMarkUp2 smalldatetime,
@MarkUp as bit

as

Select top 1 * from Patient_data..tbopd_settings;

if @@rowcount > 0

begin

Update Patient_data..tbOPd_settings

set

isAllowNewOPSTatRate = @isAllowNewOPSTatRate,
isAllowHolidayRate = @isAllowHolidayRate,
isAllowAfterFivePMRate = @isAllowAfterFivePMRate,
isAllowWeekendRate = @isAllowWeekendRate,
isAllowAfterFiveLabChemOnly = @isAllowAfterFiveLabChemOnly,
isAllowAfterFiveAllRevenues = @isAllowAfterFiveAllRevenues,
OPDStatRate = @OPDStatRate,
HolidayRate = @HolidayRate,
AfterFivePmRate = @AfterFivePmRate,
WeekendRate = @WeekendRate,
MaxMarkUpRate = @MaxMarkUpRate,
StatAndAfterFivePMRate = @StatAndAfterFivePMRate,
StatAndWeekendRate = @StatAndWeekendRate,
HolidayAndWeekend = @HolidayAndWeekend,
HolidayAndAfterFivePMRate = @HolidayAndAfterFivePMRate,
WeekendAfterFive = @WeekendAfterFive,
dtWeekendStartMarkUp = @dtWeekendStartMarkUp,
dtWeekendEndMarkUp = @dtWeekendEndMarkUp,
isAssesmentMarkup = @isAssessmentMarkUp,
isPostChargeMarkUp = @isPostChargeMarkup,
dtWeekendStartMarkUp2 = @dtWeekendStartMarkUp2,
dtWeekendEndMarkUp2 = @dtWeekendEndMarkUp2,
MarkUp = @MarkUp

end

else

begin

insert into patient_data..tbopd_settings( isAllowNewOPSTatRate, isAllowHolidayRate, isAllowAfterFivePMRate, isAllowWeekendRate,
isAllowAfterFiveLabChemOnly, isAllowAfterFiveAllRevenues,OPDStatRate,
HolidayRate, AfterFivePmRate, WeekendRate, MaxMarkUpRate,
StatAndAfterFivePMRate, StatAndWeekendRate, HolidayAndWeekend, HolidayAndAfterFivePMRate,
WeekendAfterFive, dtWeekendStartMarkUp, dtWeekendEndMarkUp, isAssesmentMarkup, isPostChargeMarkUp,
dtWeekendStartMarkUp2,dtWeekendEndMarkUp2,MarkUp)
values (@isAllowNewOPSTatRate, @isAllowHolidayRate, @isAllowAfterFivePMRate, @isAllowWeekendRate,
@isAllowAfterFiveLabChemOnly, @isAllowAfterFiveAllRevenues,@OPDStatRate,
@HolidayRate, @AfterFivePmRate, @WeekendRate, @MaxMarkUpRate,
@StatAndAfterFivePMRate, @StatAndWeekendRate, @HolidayAndWeekend, @HolidayAndAfterFivePMRate,
@WeekendAfterFive, @dtWeekendStartMarkUp, @dtWeekendEndMarkUp, @isAssessmentMarkUp,@isPostChargeMarkup,@dtWeekendStartMarkUp2,@dtWeekendEndMarkUp2,@MarkUp)

end



GO

--6/1/2012--------------------------------------------
alter table Patient_data..tbOutPatient
add isDiabet bit null

alter table Patient_data..tbOutPatient
add isHomeCare bit null

alter table patient_data..tbmaster2
add OfficeNum varchar(50)


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_UpdateMaster2')
EXEC ('CREATE PROC dbo.sp_Adm_UpdateMaster2 AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster2]
		@HospNum 		As VarChar(8),
		@BirthPlace 		As VarChar(50),
		@NationalityID 		As VarChar(2),
		@ReligionID		As VarChar(2),
		@Spouse 		As VarChar(50),
		@Mother 		As VarChar(50),
		@Father 		As VarChar(50),
		@Employer 		As VarChar(50),
		@EmployerAddress 	As VarChar(50),
		@EmployerTelNum 	As VarChar(15),
		@ContactName		As VarChar(50),
		@ContactAddress 	As VarChar(50),
		@ContactRelation 	As VarChar(50),
		@ContactTelNum  	As VarChar(15) ,
		@SpouseAddress 		varchar(50) ,
		@SpouseTelNum 		varchar(15) , 
		@FatherAddress 		varchar(50) ,
		@FatherTelNum 		varchar(15) ,
		@MotherAddress 		varchar(50) ,
		@MotherTelNum 		varchar(15) ,
		@BarangayCaptain 	varchar(50) ,
		@Position 		varchar (50) ,
		@EmploymentType 	varchar(1),
		@EmploymentLocation 	varchar(1) ,
		@EmployerFax 		varchar(15) ,
		@EmployerEmail 		varchar(50) ,
		@SpouseEmpName 		varchar(50) ,
		@SpouseEmpAddress 	varchar(50) ,
		@SpouseEmpTelNum 	varchar(15) ,
		@SpouseEmpFax 		varchar(15) ,
		@SpouseEmpEmail 	varchar(50) ,
		@PHIC			varchar(1) ,
		@Insurance		varchar(50) ,
		@HouseHold		varchar(10) ,
		@Email			varchar(50) ,
		@CellNum		varchar(15),
		@OfficeNum		varchar(50)
AS

Declare @NewHospNum VarChar(8)



Select @NewHospNum = Hospnum From tbMaster2 Where HospNum = @HospNum;

Update tbMaster set Email = @Email, CellNum = @CellNum 	Where HospNum = @HospNum;

If Len(@NewHospNum) > 0
   Begin

	Update tbMaster2 Set BirthPlace = @BirthPlace,
		NationalityID 		= @NationalityID,
		ReligionID 		= @ReligionID,
		Spouse 			= @Spouse,
		Mother 			= @Mother,
		Father 			= @Father,
		Employer 		= @Employer,
		EmployerAddress 	= @EmployerAddress,
		EmployerTelNum 	= @EmployerTelNum,
		ContactName 		= @ContactName,
		ContactAddress 		= @ContactAddress,
		ContactRelation		= @ContactRelation,
		ContactTelNum 		= @ContactTelNum ,
		SpouseAddress 		= @SpouseAddress ,
		SpouseTelNum 		= @SpouseTelNum , 
		FatherAddress 		= @FatherAddress ,
		FatherTelNum 		= @FatherTelNum ,
		MotherAddress 		= @MotherAddress ,
		MotherTelNum 		= @MotherTelNum ,
		BarangayCaptain 	= @BarangayCaptain ,
		Position 		= @Position ,
		EmploymentType 		= @EmploymentType ,
		EmploymentLocation 	= @EmploymentLocation ,
		EmployerFax 		=  @EmployerFax ,
		EmployerEmail 		= @EmployerEmail ,
		SpouseEmpName 		= @SpouseEmpName ,
		SpouseEmpAddress 	= @SpouseEmpAddress ,
		SpouseEmpTelNum 	= @SpouseEmpTelNum 	,
		SpouseEmpFax 		= @SpouseEmpFax ,
		SpouseEmpEmail 		= @SpouseEmpEmail ,
		PhilHealth		= @PHIC ,
		Insurance		= @Insurance ,
		HouseHold		= @HouseHold,
		OfficeNum		= @OfficeNum 

	Where HospNum = @HospNum ;

	
   End
Else
   Begin
	Insert Into tbMaster2 
		(HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother,
		 Father, Employer, EmployerAddress, EmployerTelNum, ContactName,
		 ContactAddress, ContactRelation, ContactTelNum ,
		SpouseAddress , SpouseTelNum , FatherAddress , FatherTelNum ,
		MotherAddress ,	 MotherTelNum ,	BarangayCaptain ,Position ,
		EmploymentType,EmploymentLocation, EmployerFax ,EmployerEmail 		 ,
		SpouseEmpName , SpouseEmpAddress , SpouseEmpTelNum ,
		SpouseEmpFax 	, SpouseEmpEmail, PhilHealth, Insurance, HouseHold, OfficeNum )
	Values	(@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother,
		 @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName,
		 @ContactAddress, @ContactRelation, @ContactTelNum,
		 @SpouseAddress , @SpouseTelNum , @FatherAddress , @FatherTelNum ,
		 @MotherAddress ,  @MotherTelNum ,  @BarangayCaptain , @Position ,
		 @EmploymentType, @EmploymentLocation, @EmployerFax , @EmployerEmail 		 ,
		 @SpouseEmpName , @SpouseEmpAddress , @SpouseEmpTelNum ,
		 @SpouseEmpFax 	, @SpouseEmpEmail, @PHIC, @Insurance, @HouseHold, @OfficeNum);
       
   End
go


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_UpdateMaster2')
EXEC ('CREATE PROC dbo.sp_Adm_UpdateMaster2 AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient]
@HospNum AS VarChar(8)
AS

if exists(SELECT * FROM	tbMaster M WHERE M.HospNum = @HospNum)
begin

Select 	M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, 
	M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode,
	ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation,
	ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, 
	ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum,  ISNULL(M.CTNum,'') CTNum,
	ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID,
	ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer,
	ISNULL(M2.EmployerAddress, '') EmployerAddress,	ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, 
	ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation,	ISNULL(M2.ContactTelNum,'') ContactTelNum, 
	ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum,
	ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, 
	ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum,
	ISNULL(M2.barangaycaptain,'') barangaycaptain ,
	ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], 
	ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , 
	ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName],
	ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], 
	ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], 
	ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum],
	ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email]
FROM 	tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum
WHERE 	M.HospNum = @HospNum

end

else

begin

Select 	M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, 
	M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode,
	M.Sex, M.BirthDate, M.CivilStatus, M.Occupation,
	M.Age, M.TelNum, M.PatientStatus, M.OPDNum, 
	M.XrayNum, M.UltraNum, M.SSSGSISNum,  M.CTNum,
	M2.BirthPlace, M2.NationalityID, M2.ReligionID,
	M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress,
	M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation,
	M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum,
	M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum,
	M2.barangaycaptain ,
	ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], 
	ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , 
	ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName],
	ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], 
	ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], 
	ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum],
	ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email]
FROM 	tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum
WHERE 	M.HospNum = @HospNum

end


go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_AOPD_SearchRevenue')
EXEC ('CREATE PROC dbo.sp_AOPD_SearchRevenue AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[sp_AOPD_SearchRevenue]
@SearchType varchar(1),
@SearchCriteria varchar(50)
AS

if @SearchType = '0' 
begin
  select r.ItemName [Description], r.RevenueID [Code]
  from BUILD_FILE..tbCoRevenueCode r
  where ISNULL(r.OPDActive, '') = 'Y'
    and r.RecordStatus = 'A'  
    and r.ItemName like @SearchCriteria + '%'
  order by r.ItemName
end

if @SearchType = '1'
begin
  /*select r.ItemName [Revenue], r.RevenueID, r.RWith [WithItems],
    r.SearchInventory, r.LocationID,
  from BUILD_FILE..tbCoRevenueCode r
	
  where ISNULL(r.OPDActive, '') = 'Y'
    and r.RecordStatus = 'A'  --AND r.revenueid = 'CT' OR REVENUEID = 'LB' OR REVENUEID = 'EM' OR REVENUEID = 'OX'--AND r.revenueid = 'EL' OR REVENUEID = 'ER' OR REVENUEID = 'EM' OR REVENUEID = 'OX'  or r.REVENUEID = 'PT' OR REVENUEID = 'MD' OR REVENUEID = 'PA'
    and r.RevenueID = @SearchCriteria
*/
  select r.ItemName [Revenue], r.RevenueID, r.RWith [WithItems],
   	r.SearchInventory, r.LocationID,b.Revenueid [InvCode]
  from BUILD_FILE..tbCoRevenueCode r
	 left join BILLING..tbBillInvCode b
	    on r.Revenueid = b.ServiceID
  where  r.RecordStatus = 'A' AND 	
	--(r.revenueid = 'EL' OR r.REVENUEID = 'ER' OR r.REVENUEID = 'EM' OR r.REVENUEID = 'OX') AND
    	r.RevenueID = @SearchCriteria
end

if @SearchType = '2'
begin
  select Count(*) [RecCount]
  from BUILD_FILE..tbCoRevenueCode r
  where ISNULL(r.OPDActive, '') = 'Y'
    and r.RecordStatus = 'A'  --AND r.revenueid = 'CT' OR REVENUEID = 'LB' OR REVENUEID = 'EM' OR REVENUEID = 'OX'
    --or r.REVENUEID = 'PT' OR REVENUEID = 'MD' OR REVENUEID = 'PA'
    and r.ItemName like @SearchCriteria + '%'
end

if @SearchType = '3' --with inventory code
begin
  select r.ItemName [Revenue], r.RevenueID, r.RWith [WithItems],
    r.SearchInventory, r.LocationID, r.StockCardRevenueID [InventoryCode]
  from BUILD_FILE..tbCoRevenueCode r
  where ISNULL(r.OPDActive, '') = 'Y'
    and r.RecordStatus = 'A'  --AND r.revenueid = 'CT' OR REVENUEID = 'LB' OR REVENUEID = 'EM' OR REVENUEID = 'OX'--AND r.revenueid = 'EL' OR REVENUEID = 'ER' OR REVENUEID = 'EM' OR REVENUEID = 'OX'  or r.REVENUEID = 'PT' OR REVENUEID = 'MD' OR REVENUEID = 'PA'
    and r.RevenueID = @SearchCriteria
end
if @SearchType = '4' 
begin
  select r.ItemName [Description], r.RevenueID [Code]
  from BUILD_FILE..tbCoRevenueCode r
  where ISNULL(r.OPDActive, '') = 'Y'
    and r.RecordStatus = 'A'
	and r.IsPatientCard ='1'
    and r.ItemName like @SearchCriteria + '%'
  order by r.ItemName 
end
go