USE [clinical_area]
GO
/****** Object:  StoredProcedure [dbo].[spGeneric_GetPatient]    Script Date: 05/11/2011 13:15:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spGeneric_GetPatient] 
@Status As Varchar (1),
@Lastname  As Varchar(20),
@HospNum  As Varchar(10),
@IDNum  As Varchar(10)

 AS

Declare @DateToDay as varchar(10);

Set @DateToDay = Convert(varchar(10),getdate(),101);

IF @Status='O' 
   Begin
    SELECT  tbOutPatient.HospNum AS [Hospital #],tbOutPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) 
     + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) 
     + '  ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS  [Patient Name],convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], tboutpatient.billingdate as [Billing Date]     
     FROM PATIENT_DATA..tbOutPatient AS tbOutPatient
	     LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster 
		ON  tbOutPatient.Hospnum = tbMaster.HospNum 
	     LEFT OUTER JOIN BUILD_FILE..tbCoCompany tbCoCompany
		ON tbOutPatient.AccountNum = tbCoCompany.AccountNum	
    where   ((tbOutPatient.AdmDate Between @DateToDay and @DateToDay + ' 23:59:59' and DcrDate is null)  
	and (tbMaster.LastName like rtrim(@Lastname) + '%' ) and tboutpatient.HospNum like @Hospnum +'%' and tbOutPatient.IDNum like @IDNum +'%') 
    	and tbOutPatient.opdstatus <> 'R'
    order by tbmaster.lastname, tbmaster.firstname, tbmaster.middlename
   End;

IF @Status = 'I'

  Begin
   SELECT tbPatient.HospNum AS [Hospital #],tbPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) 
    + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) 
    + '  ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS  [Patient Name],convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], tbpatient.billingdate as [Billing Date]
    FROM PATIENT_DATA..tbPatient AS tbPatient
    LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON  tbPatient.Hospnum = tbMaster.HospNum 
   where  (tbPatient.BillingDate Is Null)   and ((tbMaster.LastName like @Lastname + '%'  or @Lastname ='') and
	  (tbPatient.HospNum = @HospNum or @Hospnum='') and
	  (tbPatient.IDNum = @IDNum or @IDNum='') )  
   ORDER BY [Patient Name]
  End;

IF @Status = 'C'
   Begin
   SELECT tbPatient.HospNum AS [Hospital #],tbPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) 
    + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) 
    + '  ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS  [Patient Name],convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], tbpatient.billingdate as [Billing Date]
    FROM PATIENT_DATA..tbPatient AS tbPatient
    LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON  tbPatient.Hospnum = tbMaster.HospNum 
   where   (tbpatient.dcrdate <= billingdate or tbpatient.dcrdate is null)  and ((tbMaster.LastName like  @Lastname + '%' ) and
	  (tbPatient.HospNum = @HospNum or @Hospnum='') and
	  (tbPatient.IDNum = @IDNum or @IDNum='') )
   ORDER BY [Patient Name]
   End;

IF @Status = 'D'
   begin
	--OPD Previous Registration
	SELECT  tbOutPatient.HospNum AS [Hospital #],tbOutPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) 
	+ ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) 
	+ '  ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS  [Patient Name],convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], tboutpatient.billingdate as [Billing Date]     
	FROM PATIENT_DATA..tbOutPatient AS tbOutPatient
	     LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster 
		ON  tbOutPatient.Hospnum = tbMaster.HospNum 
	     LEFT OUTER JOIN BUILD_FILE..tbCoCompany tbCoCompany
		ON tbOutPatient.AccountNum = tbCoCompany.AccountNum	
	where   (tbOutPatient.OpdStatus <>'R' 
		and (tbMaster.LastName like rtrim(@Lastname) + '%' ) and tboutpatient.HospNum like @Hospnum +'%' and tbOutPatient.IDNum like @IDNum +'%') 
		and isnull(tbCoCompany.Class,'') <> 'S'
	order by tbmaster.lastname, tbmaster.firstname, tbmaster.middlename, tbOutPatient.AdmDate
   end;

IF @Status = 'T'
   begin	
	SELECT tbPatient.HospNum AS [Hospital #],tbPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) 
	  + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) 
	  + '  ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS  [Patient Name],convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], tbpatient.billingdate as [Billing Date]
	FROM PATIENT_DATA..tbPatient AS tbPatient
	    LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON  tbPatient.Hospnum = tbMaster.HospNum 
	where  ((tbMaster.LastName like @Lastname + '%'  or @Lastname ='') and
		  (tbPatient.HospNum = @HospNum or @Hospnum='') and
		  (tbPatient.IDNum = @IDNum or @IDNum='') )  
	ORDER BY [Patient Name], [Admission Date]
   end;

----------------------------------------------------------------------------
USE [clinical_area]
GO
/****** Object:  StoredProcedure [dbo].[spGeneric_CheckPending]    Script Date: 05/11/2011 13:16:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[spGeneric_CheckPending]
@userid varchar(10)
 AS

select count(*) TotalPending
from Station..tbNurseCommunicationFile
where (recordstatus IS NULL or recordstatus='') 
and RevenueID in (Select revenueid from tbGenericAccess where userid = @userid)

----------------------------------------------------------------------------
USE [Clinical_area]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

----->>Created by oski for Generic Charging

--patient_data..spGeneric_GetPatient 'I', '131844', '176666'

CREATE Procedure [dbo].[spGeneric_GetPatientInfo] 
@strPatientType as varchar(1),
@HospNum as varchar(10),
@Idnum as varchar(11)

as
if @strpatientType = 'O' 
begin
	Select isnull(A.LastName,'') LastName,
		   isnull(A.FirstName,'') FirstName,
		   isnull(A.MiddleName,'') MiddleName,
		   isnull(A.birthDate,'')BirthDate,
           isnull(A.Sex,'') Sex,
		   isnull(A.Age,'') Age,
		   isnull(A.Barangay,'')Barangay,
		   Case A.CivilStatus
			When '0' Then 'Child'
			When '1' Then 'Single'
			When '2' then 'Married'
			When '3' Then 'Widow'
			when '4' Then 'Separated'
			When '5' Then 'Divorced'
			Else '' End AS CivilStatus,
	       B.Idnum,
           B.HospNum,
           B.DoctorID1 as DoctorID,
		   'Dr. '+ D.LastName + ', ' + D.FirstName as Doctor,
           'OPD' as Room,
		   'A' as RoomClassID,
           B.AdmDate,
           case when (B.AccountNum = B.Hospnum or isnull(B.AccountNum,'') = '') then 'PERSONAL'
					else C.Company end as Account,
		   B.AccountNum
	from patient_Data..tboutpatient B
        left outer join patient_data..tbmaster A on a.hospnum = b.hospnum
		left outer join build_file..tbcocompany C on b.accountnum = c.accountnum
        left outer join build_file..tbcodoctor D on b.doctorid1 = d.doctorid
where B.Hospnum = @hospnum and b.idnum = @idnum

end
if @strpatientType = 'I' 
	begin
	Select isnull(A.LastName,'') LastName,
		   isnull(A.FirstName,'') FirstName,
		   isnull(A.MiddleName,'') MiddleName,
		   isnull(A.birthDate,'')BirthDate,
           isnull(A.Sex,'') Sex,
		   isnull(A.Age,'') Age,
		   isnull(A.Barangay,'')Barangay,
		   Case A.CivilStatus
			When '0' Then 'Child'
			When '1' Then 'Single'
			When '2' then 'Married'
			When '3' Then 'Widow'
			when '4' Then 'Separated'
			When '5' Then 'Divorced'
			Else '' End AS CivilStatus,
	       B.Idnum,
           B.HospNum,
           B.AttendingDr1 as Doctorid,
           'Dr. '+ D.LastName + ', ' + D.FirstName as Doctor,
           B.RoomiD as Room,
		   R.RoomClassID, 
           B.AdmDate,
           case when (B.AccountNum = B.Hospnum or isnull(B.AccountNum,'') = '') then 'PERSONAL'
					else C.Company end as Account,
           B.AccountNum
	from patient_Data..tbpatient B
        left outer join patient_data..tbmaster A on a.hospnum = b.hospnum
		left outer join build_file..tbcocompany C on b.accountnum = c.accountnum
		left outer join build_file..tbcodoctor D on d.doctorid = b.attendingdr1
		left outer join build_File..tbcoroom R on b.roomid = r.roomid

where B.Hospnum = @hospnum and b.idnum = @idnum

end
----------------------------------------------------------------------------

Alter table Patient_Data..tbhospitalinfo
add isAllowSpecializedCompanyRate bit null

----------------------------------------------------------------------------
USE [build_file]
GO
/****** Object:  Table [dbo].[tbcoBuildFileSetting]    Script Date: 05/11/2011 14:40:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbcoBuildFileSetting](
	[isAllowCompanyItems] [bit] NOT NULL DEFAULT(0),
	[AllowAutoDoctorCode] [bit] NOT NULL DEFAULT(0),
	[AllowAutoCompanyCode] [bit] NOT NULL DEFAULT(0),
	[AllowMoAonCompany] [bit] NULL
) ON [PRIMARY]
----------------------------------------------------------------------------

ALTER TABLE [dbo].[tbCoCompany] ADD [moaStartDate] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
			[moaEndDate] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
			[moNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
			[compPassword] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
			[moaNumber] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
			[isEmployee] [bit] NULL

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

USE [Build_File]


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[sp_Bui_Search_Company](@SearchCriteria as varchar(60), @type as varchar(1))
AS

If (@Type = '1')
   Select '' as dumm, isnull(a.company,'') as Company, isnull(a.accountnum,'')as AccountNum, isnull(b. description,'') as Description, isnull(a.cardcode,'') as CardCode,
	 (case when a.status = 'A' then 'Active'
                       else 'Inactive' End) as status
   from tbcocompany a left outer join tbcobusinessclass b
           on a.class = b.code
   order by a.company
else
If (@Type = '2')
   Select isnull(a.accountnum,'') as AccountNum, isnull(a.company,'') as Company, isnull(a.housestreet,'') as HouseStreet, isnull(a.barangay,'') as Barangay, isnull(a.zipcode,'') as Zipcode, isnull(a.status,'') as Status,isnull(a.cardcode,'') as CardCode,
             isnull(a.tin,'') as TIN, isnull(a.vat,'') as VAT, isnull(a.fax,'') as FAX, isnull(a.status,'') as Status, isnull(a.phone,'') as Phone,
             (case when ltrim(rtrim(isnull(b.description,''))) = '' then '(NONE)'
  	          else b.description End) as description,
             (case when ltrim(rtrim(isnull(c.town,'') + ', ' + isnull(c.province,''))) = ',' then '(NONE)'
                      else isnull(c.town,'') + ', ' + isnull(c.province,'') End) as province, IsNull( a.Class, '' ) as Class ,
	IsNull( a.ContactPerson, '' ) as ContactPerson, IsNull( a.Position, '' ) as Position, IsNull( a.Officer, '' ) as Officer, 
	isnull(CardCode, '') as CardCode, isnull(EmployeeID,'') as EmployeeID, isnull(a.CreditLimit, 0) as CreditLimit,  CompanyDiscount, 
	moaStartDate,  moaEndDate , moaNumber, comppassword, isnull(a.isEmployee,0) as isEmployee
   from tbcocompany a left outer join tbcobusinessclass b
           on a.class = b.code
           left outer join tbcoaddress c
           on a.zipcode = c.zipcode
   where a.accountnum = @searchcriteria

else
If (@type = '3')
    Select * from tbcoCompany where ltriM(rtrim(isnull(company,''))) = @SearchCriteria

else
If (@Type = '4')
   Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, b.code,a.cardcode,
                       (case when a.status = 'A' then 'Active'
                       else 'Inactive' End) as status
   from tbcocompany a left outer join tbcobusinessclass b
           on a.class = b.code
   order by b.description,a.company

else
If (@Type = '5')
   Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code,isnull(a.cardcode,'') as Cardcode,
                       (case when a.status = 'A' then 'Active'
                       else 'Inactive' End) as status
   from tbcocompany a left outer join tbcobusinessclass b
           on a.class = b.code
   order by a.accountnum

else
If (@Type = '6')
   Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code,isnull(a.cardcode,'') as Cardcode,
                       (case when a.status = 'A' then 'Active'
                       else 'Inactive' End) as status
   from tbcocompany a left outer join tbcobusinessclass b
           on a.class = b.code
   order by a.company

else
If (@Type = '7')
   Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as Accountnum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code, isnull(a.cardcode,'') as CardCode,
                       (case when a.status = 'A' then 'Active'
                       else 'Inactive' End) as status
   from tbcocompany a left outer join tbcobusinessclass b
	           on a.class = b.code
   where  b.description  not like '%advance%'
   order by a.company

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON