USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[spGlobal_RegisteredOPD]    Script Date: 11/21/2017 15:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGlobal_RegisteredOPD] 
	@Current varchar(1),
	@SearchType varchar(1),
	@SearchCriteria varchar(50)
AS
SET NOCOUNT ON;
declare @Today datetime

set @Today = cast(convert(varchar(10), getdate(), 101) as datetime)

Declare @LastName varchar(30),
		@FirstName varchar(30),
		@MiddleName varchar(30)

Set @FirstName = (select ltrim(rtrim(strName)) from Laboratory.dbo.fn_LabSplit(@SearchCriteria,',') where code = 2);
Set @MiddleName = (select ltrim(rtrim(strName)) from Laboratory.dbo.fn_LabSplit(@SearchCriteria,',') where code = 3);
Set @LastName = (select ltrim(rtrim(strName)) from Laboratory.dbo.fn_LabSplit(@SearchCriteria,',') where code = 1);

IF @SearchType = '0'
	BEGIN
		if @Current = 0
			Begin
				select Case When(IsNull(P.ErNum,'') <> '') Then '*' Else '' End [ER],
					p.idnum [Adm. #], isnull(m.lastname,'') [Last Name], 
					isnull(m.firstname,'') [First Name], isnull(m.middlename,'') [Middle Name], 
					CONVERT(VARCHAR, p.admdate, 101) [Adm. Date]
				from Patient_Data..tbOutPatient p
					left join Patient_Data..tbMaster m   on p.hospnum = m.hospnum
					left join Build_File..tbCoCompany C   On p.Accountnum = C.AccountNum
				where M.LastName like ltrim(rtrim(IsNull(@LastName,''))) + '%' AND
					M.FirstName like ltrim(rtrim(IsNull(@FirstName,''))) + '%' AND
					M.MiddleName like ltrim(rtrim(IsNull(@MiddleName,''))) + '%'
					and P.Dcrdate is not NULL 
					and P.LateCharges = 1
					and isnull(C.Class,'') <> 'S' and isnumeric(M.HospNum) = 1
				order by m.lastname, m.firstname, m.middlename
			End
		else
			Begin
				select Case When(IsNull(P.ErNum,'') <> '') Then '*' Else '' End [ER],
					p.idnum [Adm. #], isnull(m.lastname,'') [Last Name], 
					isnull(m.firstname,'') [First Name], isnull(m.middlename,'') [Middle Name], 
					CONVERT(VARCHAR, p.admdate, 101) [Adm. Date]
				from Patient_Data..tbOutPatient p
					left join Patient_Data..tbMaster m   on p.hospnum = m.hospnum
					left join Build_File..tbCoCompany C   On p.Accountnum = C.AccountNum
				where M.LastName like ltrim(rtrim(IsNull(@LastName,''))) + '%' AND
					M.FirstName like ltrim(rtrim(IsNull(@FirstName,''))) + '%' AND
					M.MiddleName like ltrim(rtrim(IsNull(@MiddleName,''))) + '%'
					and p.admdate >= @Today
					and isnull(C.Class,'') <> 'S' and isnumeric(M.HospNum) = 1
				order by m.lastname, m.firstname, m.middlename
			End	 
	END

IF @SearchType = '1'
	BEGIN
		if @Current = 0
			Begin
				  select	isnull(p.idnum,'') [IDNum], isnull(p.hospnum,'') [HospNum], 
							isnull(m.lastname,'') [LastName], isnull(m.firstname,'') [FirstName], 
							isnull(m.middlename,'') [MiddleName], 
    						isnull(p.admdate,'') [AdmDate], 
    						isnull(p.dcrdate,'') [DCRDate], 
    						isnull(convert(varchar(50), p.billingdate, 100),'') [BillingDate],
    						isnull(p.accountnum,'') [AccountNum],
							case 
								  when isnull(p.accountnum, '') = p.hospnum then 'PERSONAL'
								  when isnull(p.accountnum, '') = '' then 'PERSONAL'
								else c.company
							end as company,
							c.Class [CompanyClassID],
							m.civilstatus,
							m.sex,
							case 
							  when m.birthdate is null then ''
							  when m.birthdate <= '01/01/1900' then ''
							  else convert(varchar(10), m.birthdate, 101)
							end as birthdate,
							case 
							  when m.birthdate is null then m.age
							  when m.birthdate <= '01/01/1900' then m.age
							  else cast(datediff(year, m.birthdate, getdate()) as varchar(3))
							end as age,
							m.housestreet, m.barangay,
							a.town, a.province,
						   (Select Laboratory.dbo.fn_LabGetPatientType(P.IDNum)) as PatientType
				  from Patient_Data..tbOutPatient p
					left join Patient_Data..tbMaster m
					  on p.hospnum = m.hospnum
					left join Build_File..tbCocompany c
					  on p.accountnum = c.accountnum
					left join Build_File..tbCoaddress a
					  on m.zipcode = a.zipcode
				  where p.idnum = @SearchCriteria 
						and isnumeric(M.HospNum) = 1
						and ((p.dcrdate is null and p.admdate > = @Today)
						or (p.dcrdate is not null and P.LateCharges = 1))
			End
		Else
			Begin
				  select	isnull(p.idnum,'') [IDNum], isnull(p.hospnum,'') [HospNum], 
							isnull(m.lastname,'') [LastName], isnull(m.firstname,'') [FirstName], 
							isnull(m.middlename,'') [MiddleName], 
    						isnull(p.admdate,'') [AdmDate], 
    						isnull(p.dcrdate,'') [DCRDate], 
    						isnull(convert(varchar(50), p.billingdate, 100),'') [BillingDate],
    						isnull(p.accountnum,'') [AccountNum],
							case 
								  when isnull(p.accountnum, '') = p.hospnum then 'PERSONAL'
								  when isnull(p.accountnum, '') = '' then 'PERSONAL'
								else c.company
							end as company,
							c.Class [CompanyClassID],
							m.civilstatus,
							m.sex,
							case 
							  when m.birthdate is null then ''
							  when m.birthdate <= '01/01/1900' then ''
							  else convert(varchar(10), m.birthdate, 101)
							end as birthdate,
							case 
							  when m.birthdate is null then m.age
							  when m.birthdate <= '01/01/1900' then m.age
							  else cast(datediff(year, m.birthdate, getdate()) as varchar(3))
							end as age,
							m.housestreet, m.barangay,
							a.town, a.province,
						   (Select Laboratory.dbo.fn_LabGetPatientType(P.IDNum)) as PatientType
				  from Patient_Data..tbOutPatient p
					left join Patient_Data..tbMaster m
					  on p.hospnum = m.hospnum
					left join Build_File..tbCocompany c
					  on p.accountnum = c.accountnum
					left join Build_File..tbCoaddress a
					  on m.zipcode = a.zipcode
				  where p.idnum = @SearchCriteria 
						and isnumeric(M.HospNum) = 1
						and p.admdate > = @Today
			End
	END

IF @SearchType = '2'
	BEGIN
		  select count(*) [RecCount]
		  from Patient_Data..tbOutPatient p
			left join tbmaster m
			  on p.hospnum = m.hospnum
		  where M.LastName like ltrim(rtrim(IsNull(@LastName,''))) + '%' AND
			M.FirstName like ltrim(rtrim(IsNull(@FirstName,''))) + '%' AND
			M.MiddleName like ltrim(rtrim(IsNull(@MiddleName,''))) + '%'
			  and isnumeric(M.HospNum) = 1
	END

IF @SearchType = '4'
	BEGIN
		  select p.idnum, p.hospnum, m.lastname, m.firstname, m.middlename, 
			isnull(p.admdate,'') [admdate], isnull(p.dcrdate,'') [dcrdate], isnull(p.billingdate,'') [billingdate],
			isnull(p.accountnum,'') [accountnum],
			case 
			  when isnull(p.accountnum, '') = p.hospnum then 'PERSONAL'
			  when isnull(p.accountnum, '') = '' then 'PERSONAL'
			  else c.company
			end as company,
			m.civilstatus,
			m.sex,
			case 
			  when m.birthdate is null then ''
			  when m.birthdate <= '01/01/1900' then ''
			  else convert(varchar(10), m.birthdate, 101)
			end as birthdate,
			case 
			  when m.birthdate is null then m.age
			  when m.birthdate <= '01/01/1900' then m.age
			  else cast(datediff(year, m.birthdate, getdate()) as int)
			end as age,
			m.housestreet, m.barangay,
			a.town, a.province
		  from Patient_Data..tbOutPatient p
			left join Patient_Data..tbMaster m
			  on p.hospnum = m.hospnum
			left join Build_File..tbCocompany c
			  on p.accountnum = c.accountnum
			left join Build_File..tbCoaddress a
			  on m.zipcode = a.zipcode
		  where M.LastName like ltrim(rtrim(IsNull(@LastName,''))) + '%' AND
				M.FirstName like ltrim(rtrim(IsNull(@FirstName,''))) + '%' AND
				M.MiddleName like ltrim(rtrim(IsNull(@MiddleName,''))) + '%'
			and isnumeric(M.HospNum) = 1
		  order by m.lastname
	END

IF @SearchType = '5'
	BEGIN
		select Case When(Max(IsNull(P.ErNum,'')) <> '') Then '*' Else '' End [ER],
			Max(p.idnum) [Adm. #], Max(isnull(m.lastname,'')) [Last Name], 
			Max(isnull(m.firstname,'')) [First Name], Max(isnull(m.middlename,'')) [Middle Name], 
			Max(CONVERT(VARCHAR, getdate(), 101)) [Adm. Date]
		from Patient_Data..tbOutPatient p
			left join Patient_Data..tbMaster m   on p.hospnum = m.hospnum
		where M.LastName like ltrim(rtrim(IsNull(@LastName,''))) + '%' AND
			M.FirstName like ltrim(rtrim(IsNull(@FirstName,''))) + '%' AND
			M.MiddleName like ltrim(rtrim(IsNull(@MiddleName,''))) + '%' AND
			M.LastName <> '' and
			p.admdate >= @Today
		group by P.HospNum, m.lastname, m.firstname, m.middlename
		order by m.lastname, m.firstname, m.middlename
	END