use Patient_Data
GO

alter table tbmaster2 add PHICNum varchar(50)
GO

alter table tbmaster alter column CardNumber varchar(50)
GO

/****** changed b.cardnum to a.cardnum ******/
ALTER PROCEDURE [dbo].[sp_AOPD_LoadOutPatient]
     @IDNum as varchar(8)
AS

    Select '' as dumm, isnull(a.lastname,'') + ', ' + isnull(a.firstname,'') + ' ' +  isnull(a.middlename,'') as patientname,
		isnull(a.lastname,'') as LastName, isnull(a.firstname,'')  as FirstName, isnull(a.middlename,'') as middlename,
                b.idnum, a.hospnum,
               (SELECT CASE WHEN b.ADMDATE IS NULL THEN '' 
                                       ELSE b.ADMDATE END) AS ADMDATE,
               (SELECT CASE WHEN b.DCRDATE IS NULL THEN '' 
                                      ELSE  b.DCRDATE END) AS DCRDATE,
	       b.OPDStatus,
               	isnull(H.Weight,'') as Weight,
	       	isnull(h.Temperature,'') as Temperature,
	       	isnull(h.BloodPressure,'') as BloodPressure,
		isnull(a.bloodtype,'') as bloodtype,
	        isnull(h.DiagnosisID1,'') as DiagnosisID1, 	
		isnull(h.DiagnosisID2,'') as DiagnosisID2, 	
		isnull(h.DiagnosisID3,'') as DiagnosisID3, 	
		isnull(h.findings,'') as Findings,
		isnull(h.medications,'') as medications,
	       	isnull(b.AccountNum,'') as AccountNum,
	       	isnull(b.Remarks,'') as Remarks,
	       	isnull(b.ServiceID2,'') as ServiceID2,
		isnull(b.ServiceID3,'') as ServiceID3,
		isnull(b.ServiceID4,'') as ServiceID4,
		isnull(b.ServiceID5,'') as ServiceID5,
		isnull(b.ServiceID1,'') as ServiceID1,
		isnull(b.DoctorID1,'') as DoctorID1,
	       	isnull(b.DoctorID2,'') as DoctorID2,		 	       		
		isNull(b.hosPlan,'') as HospPlan,
		isnull(c.Company,'') as Company,
		isnull(b.ConsultationType,'') as ConsultationType,
		isnull(G.GuarantorName,'') as GuarantorName,
		isnull(G.GuarantorAddress,'') as GuarantorAddress,
		isnull(G.GuarantorEmployer,'') as GuarantorEmployer,
		isnull(G.GuarantorTelNum,'') as GuarantorTelNum,
		isnull(I.InformantName,'') as InformantName,
		isnull(I.InformantAddress,'') as InformantAddress,
		isnull(I.InformantRelation,'') as InformantRelation,
		isnull(I.InformantTelNum,'') as InformantTelNum,
                isnull(EmployerAddress,'') as GuarantorEmployerAddress,
		a.bloodtype as bloodtype, b.ADMDATE as AdmissionDate,
		b.PatientType as PatientType,
		b.ConType as ConType,
		cc.ChiefComplaintID as ChiefComplaintID1, CC.ChiefComplaint as ChiefComplaint1,
		cc2.ChiefComplaintID as ChiefComplaintID2, CC2.ChiefComplaint as ChiefComplaint2,
		cc3.ChiefComplaintID as ChiefComplaintID3, CC3.ChiefComplaint as ChiefComplaint3,
		isnull(b.paycode,'') as Paycode,
		isnull(a.CardNumber,'') as CardNumber,
		isnull(B.SeniorCitizen,'') as SeniorCitizenID,		
		isnull(A.SeniorCitizenID,'') as SeniorCitizenIDNo,
		(b.pharmaSeniorDiscounted) as SeniorCitizen,
		isnull(b.PerUnit,'') PerUnit,
		isnull(b.CauseofConsultationID,'') as CauseofConsultationID, a.Age,
		dbo.fn_GetPatientCivilStatus(a.Hospnum) as CivilStatus, H.ChiefComplaints,
		isnull(H.Procedures,'') Procedures,
		isnull(H.Condition, '') Condition, isnull(H.RespiratoryRate, '') RespiratoryRate,
		isnull(H.AnesthesiaType, '') AnesthesiaType,
		isnull(B.DoctorID3, '') DoctorID3, 
		isnull(B.OperationDate, getdate()) OperationDate,
		isnull(isWalkIn,0) isWalkIn, isnull(B.HMOApprovalNum,'') HMOApprovalNum,
		isnull(Jonelta,0) Jonelta,
		isnull(B.chiefcomplaintid1,'') as CauseofConsultationid1,
		isnull(CC.ChiefComplaint,'') as CauseofConsultation1,
		isnull(B.chiefcomplaintid2,'') as CauseofConsultationid2,
		isnull(CC2.ChiefComplaint,'') as CauseofConsultation2, 	
		isnull(B.chiefcomplaintid3,'') as CauseofConsultationid3, 
		isnull(CC3.ChiefComplaint,'') as CauseofConsultation3,
		isnull(H.Height, '') as Height, 
		isnull(H.PulseRate,'') as PulseRate,
   ----additional
		isnull(b.DoctorID3,'') as DoctorID3,
		isnull(b.DoctorID4,'') as DoctorID4,
		isnull(b.DoctorID5,'') as DoctorID5,
		---isnull(b.Ismabrate,0) as isMabrate,
		isnull(B.HomeService,'N')as HomeService
               from tboutpatient b 
		left outer join tbmaster a on b.hospnum = a.hospnum 
		left outer join Build_File..tbCoCompany C on b.AccountNum = c.AccountNum	
		left outer join tbOutPatientHistory H on b.IDNum=H.IDnum 
		Left Outer Join tbPatientInformant I on b.IDNum = I.IDNum 
		left Outer Join tbPatientGuarantor G on b.IDNum = G.IDNum  		
		left outer join BUILD_FILE..tbCoChiefComplaint CC on b.ChiefComplaintID1 = CC.ChiefComplaintID
		left outer join BUILD_FILE..tbCoChiefComplaint CC2 on b.ChiefComplaintID2 = CC2.ChiefComplaintID
		left outer join BUILD_FILE..tbCoChiefComplaint CC3 on b.ChiefComplaintID3 = CC3.ChiefComplaintID
	       where b.IDNum = @IDNum

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

GO



create table tbOPD_UserLogin
(
EmployeeID varchar(20) null,
Remarks text null,
LoginDate datetime null,
LogOutDate datetime null,
LogSequence float null
)
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_OPD_Search_User')
EXEC ('CREATE PROC dbo.sp_OPD_Search_User AS SELECT 1')
GO




ALTER procedure [dbo].[sp_OPD_Search_User]

(@Type as varchar(1), @UserID as varchar(5), @Date as varchar(10))



AS



if @Type = '1'

	select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks

	from TbOPD_userlogin A 

	left outer join password..tbpasswordmaster B

		on A.EmployeeID = B.EmployeeID

	--where A.EmployeeID = @UserID

	order by A.logsequence desc

else

if @Type = '2'

	select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks

	from TbOPD_userlogin A 

	left outer join password..tbpasswordmaster B

		on A.EmployeeID = B.EmployeeID

	where A.LogSequence = @UserID

	order by A.logsequence desc

else

if @Type = '3'

	select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks

	from TbOPD_userlogin A 

	left outer join password..tbpasswordmaster B

		on A.EmployeeID = B.EmployeeID


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_OPD_UserLogin')
EXEC ('CREATE PROC dbo.SP_OPD_UserLogin AS SELECT 1')
GO





ALTER procedure [dbo].[SP_OPD_UserLogin]
	@LogSequence as varchar(5),
	@EmployeeID as varchar(50),
	@Remarks as Text,
	@LogInDate as datetime,
	@LogOutDate as datetime

As

Declare @strRemarks as varchar(500);

if Exists(Select * From patient_data..TbOPD_UserLogin Where EmployeeID = @EmployeeID and LogOutDate is null)
	Begin
		set @strRemarks =  (Select top 1 Cast(Remarks as varchar(1000))  From TbOPD_UserLogin 
				   Where EmployeeID = @EmployeeID and LogOutDate is null) + ' Not Properly Logout' 
		Update TbOPD_UserLogin 
			set LogOutDate = getdate(),
			      Remarks = @strRemarks		
		Where EmployeeID = @EmployeeID and LogOutDate is null;
	End;

If @LogoutDate = '' 
	Begin
		Set @LogoutDate = Null
	End;

insert into patient_data..TbOPD_UserLogin(LogSequence, EmployeeID, Remarks, LogInDate, LogOutDate)
	values(@LogSequence, @EmployeeID, @Remarks, GetDate(), @LogoutDate)




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_OPD_UpdateLogRemarks')
EXEC ('CREATE PROC dbo.sp_OPD_UpdateLogRemarks AS SELECT 1')
GO





ALTER procedure [dbo].[sp_OPD_UpdateLogRemarks]
	@EmployeeID as varchar(10),
	@Remarks as text

AS

Update  TbOPD_UserLogin
	Set Remarks = @Remarks
Where EmployeeID = @EmployeeID and LogOutDate is null;


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_OPD_UserLogin_Update')
EXEC ('CREATE PROC dbo.SP_OPD_UserLogin_Update AS SELECT 1')
GO




ALTER procedure [dbo].[SP_OPD_UserLogin_Update]
@EmployeeID as varchar(50)

as


Update patient_data..TbOPD_UserLogin 
	Set LogoutDate = Getdate()
Where EmployeeId= @EmployeeID and LogoutDate = null;

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