USE [Laboratory]
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendFax]
@VerifyDate varchar(10),
@VerifyDateEnd varchar(10)= @VERIFYDATE
AS


/*IF @VerifyDate = ''
	BEGIN
		SET @VerifyDateEnd = @VerifyDate
	END*/

SELECT LM.RequestNum, LM.RefNum, LM.UserID, 
	LM.FaxResult, LM.QueueTag, LM.SectionID,
	LLB.VerifyDate, LLB.AccessionNum, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	CLS.LabSection, CLE.LabExam,
	PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE (LLB.VerifyDate between @VerifyDate  and @VerifyDateEnd + ' 23:59:59.99') 
	AND  LM.FaxResult = 'Y' and LM.FaxStatus = null and LM.RequestStatus = 'W'
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L') AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_DeleteFaxNumber]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_DeleteFaxNumber]

@HospNum varchar(10),
@SortOrder int

AS

delete Patient_data..tbPatientFaxNumber
where HospNum = @HospNum and SortOrder = @SortOrder
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_GetFaxNumber]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendFax_GetFaxNumber]

@HospNum varchar(10)
AS

SELECT PFN.FaxNumber
FROM Patient_Data..tbPatientFaxNumber PFN
WHERE PFN.HospNum = @HospNum
ORDER BY PFN.SortOrder




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_GetFaxNumbers]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_GetFaxNumbers]

@SearchString varchar(20),
@Mode varchar(1) = '0'

AS

if @Mode = '0' 
	begin
		SELECT PEA.FaxNumber, PEA.SortOrder, M.HospNum, 
			isnull(M.LastName,'') as LastName, 
			isnull(M.FirstName,'') as FirstName, 
			isnull(M.MiddleName,'') as MiddleName 
		FROM Patient_Data..tbMaster M
			LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PEA on M.HospNum = PEA.HospNum
		WHERE M.HospNum = @SearchString or M.LastName like @SearchString + '%'
		
		UNION ALL
		
		SELECT PEA.FaxNumber, PEA.SortOrder, M.HospNum, 
			isnull(M.LastName,'') as LastName, 
			isnull(M.FirstName,'') as FirstName, 
			isnull(M.MiddleName,'') as MiddleName 
		FROM Patient_Data..tbcashpatient M
			LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PEA on M.HospNum = PEA.HospNum
		WHERE M.HospNum = @SearchString or M.LastName like @SearchString + '%'
		ORDER BY M.LastName, M.HospNum
	end
else 
	begin
		SELECT PEA.FaxNumber, PEA.SortOrder, M.HospNum, 
			isnull(M.LastName,'') as LastName, 
			isnull(M.FirstName,'') as FirstName, 
			isnull(M.MiddleName,'') as MiddleName 
		FROM Patient_Data..tbMaster M
			LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PEA on M.HospNum = PEA.HospNum
		WHERE M.HospNum = @SearchString
		
		UNION ALL
		
		SELECT PEA.FaxNumber, PEA.SortOrder, M.HospNum, 
			isnull(M.LastName,'') as LastName, 
			isnull(M.FirstName,'') as FirstName, 
			isnull(M.MiddleName,'') as MiddleName 
		FROM Patient_Data..tbCashPatient M
			LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PEA on M.HospNum = PEA.HospNum
		WHERE M.HospNum = @SearchString
		ORDER BY M.LastName, M.HospNum
	end
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_InsertFaxNumber]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_InsertFaxNumber]

@HospNum varchar(10),
@FaxNumber varchar(100),
@SortOrder int

AS

insert into Patient_data..tbPatientFaxNumber (HospNum, FaxNumber, SortOrder)
values (@HospNum, @FaxNumber, @SortOrder)
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_isBatchResult]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_isBatchResult]
@RefNum varchar(30),
@SectionID varchar(3)
AS

SELECT LM.RequestNum, LM.RefNum, LM.SectionID, 
	V.Code, isnull(V.ResultName,V.ChargingName) as ExamName,
	V.formType, V.Valuetype
FROM Laboratory..tbLabMaster LM
	left outer join Build_File..tbCoLabValues V 
		on LM.ItemId = V.Code
WHERE LM.Refnum = @RefNum and V.FormType in ('1', '7', 'U', '5', 'P') 
	and LM.SectionId = ltrim(rtrim(@SectionID))
ORDER BY LM.RequestNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_MarkUnsent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[Lab_SendFax_MarkUnsent]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET FaxStatus = 'U'
WHERE RequestNum LIKE @RequestNum

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'FAX MARK UNSENT - ' + @RequestNum)
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_MarkUnsent_Group]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendFax_MarkUnsent_Group]
@VerifyDate varchar(10),
@VerifyDateEnd varchar(10) = @VerifyDate
AS

UPDATE Laboratory..tbLabMaster
SET FaxStatus = 'U'
from Laboratory..tbLabMaster LM
LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
where (LLB.VerifyDate between @VerifyDate and @VerifyDateEnd + ' 23:59:59.99') 
	and (FaxStatus is null or FaxStatus = 'P')
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_PauseSending]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Lab_SendFax_PauseSending]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET FaxStatus = 'P'
WHERE RequestNum LIKE @RequestNum

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'FAX PAUSE SENDING - ' + @RequestNum);

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_Refax]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO





CREATE PROCEDURE [dbo].[Lab_SendFax_Refax]
@VerifyDate varchar(10)
AS

SELECT LM.RequestNum, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 	
	LLB.VerifyDate, 
	CLS.LabSection, 
	CLE.LabExam, 
	LM.HospNum, 
	PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' and convert(varchar(10),LLB.VerifyDate,101) = convert(varchar(10),@VerifyDate,101) 
    and  LM.FaxResult = 'Y' and LM.FaxStatus = 'S' AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate



GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_Refax_Unsent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendFax_Refax_Unsent]
@VerifyDate varchar(10)
AS

SELECT LM.RequestNum, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, 
	CLS.LabSection, 
	CLE.LabExam, 
	LM.HospNum, 
	PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' and convert(varchar(10),LLB.VerifyDate,101) = @VerifyDate 
	and  LM.FaxResult = 'Y' and LM.FaxStatus = 'U' AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate


GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_ReportStatus]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_ReportStatus]
@StartDate as varchar(10),
@EndDate as varchar(10),
@Status as varchar(1)

AS

select LM.HospNum, LM.requestnum, LM.IdNum, LM.QueueTag,
	LM.RefNum, LM.Transdate as DateCharged,
	VM.LastName, VM.Firstname, VM.MiddleName, 
	E.LabExam, S.LabSection, LB.VerifyDate
from LABORATORY..tblablogbook LB
	left outer join tblabMaster LM on LM.requestnum = LB.requestNum	
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
	left outer join BUILD_FILE..tbCoLabExam E on LM.ItemID = E.LabExamID
	left outer join BUILD_FILE..tbColabSection S on LM.SectionID = S.LabSectionID
where LM.FaxStatus = @Status and LB.Verifydate between @StartDate and @EndDate + ' 23:59:59.99'
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L') and LM.FaxResult='Y'
	AND LM.ItemId <> '198'
order by LB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_ReportStatus_PerSection]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_ReportStatus_PerSection]
@StartDate as varchar(10),
@EndDate as varchar(10),
@Status as varchar(1),
@SectionID as varchar(3)

AS

select LM.HospNum, LM.Requestnum, LM.IdNum, LM.QueueTag,
	LM.RefNum, LM.Transdate as DateCharged, 
	VM.LastName, VM.Firstname, VM.MiddleName, 
	E.LabExam, S.LabSection, LB.verifyDate
from LABORATORY..tblablogbook LB
	left outer join tblabMaster LM on LM.requestnum = LB.requestNum	
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
	left outer join BUILD_FILE..tbCoLabExam E on LM.ItemID = E.LabExamID
	left outer join BUILD_FILE..tbColabSection S on LM.SectionID = S.LabSectionID
where LM.FaxStatus = @Status and (LB.Verifydate between @StartDate and @EndDate + ' 23:59:59.99')
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L') and LM.SectionId = @SectionID and FaxResult='Y'
	AND LM.ItemId <> '198'
order by LB.VerifyDate

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_ResumeSending]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Lab_SendFax_ResumeSending]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET FaxStatus = null
WHERE RequestNum LIKE @RequestNum

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'FAX RESUME - ' + @RequestNum);
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SearchChargeSlip]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SearchChargeSlip]

@RefNum varchar(30)

AS

select top 1 HospNum from Laboratory..tbLabMaster where RefNum = @RefNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SetfgridQueue]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SetfgridQueue]

@VerifyDate varchar(10)
AS

SELECT LM.HospNum, LM.FaxStatus, LM.RequestNum, 
	LM.RefNum, LM.SectionID, LM.QueueTag, LM.SpecimenID,
	CLS.LabSection, CLE.LabExam,	
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, LLB.AccessionNum, ISNULL(PFN.FaxNumber,'') AS FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum and PFN.SortOrder = 1
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') 
	AND LM.RequestStatus = 'W' AND LM.FaxResult = 'Y' 
	AND (LM.FaxStatus is null OR  LM.FaxStatus = 'P') 
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
	AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SetlstSent_Search]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SetlstSent_Search]

@VerifyDate varchar(10),
@SearchKey varchar(50)
AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.FaxResult,
	CLS.LabSection, CLE.LabExam, LM.QueueTag,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, LLB.RequestNum, 
	LLB.AccessionNum, PFN.FaxNumber,
	LM.IDNUM
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum and PFN.SortOrder = 1
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.FaxResult = 'Y' AND LM.FaxStatus = 'S' 
	AND (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99')	
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
	AND LM.ItemId <> '198'
ORDER BY VM.LastName, VM.FirstName, VM.MiddleName, LLB.VerifyDate





GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SetlstSent_SearchAll]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SetlstSent_SearchAll]

@SearchKey varchar(50)
AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.FaxResult,
	CLS.LabSection, CLE.LabExam, LM.QueueTag,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	convert(varchar(10),LLB.VerifyDate,101) VerifyDate, 
	LLB.RequestNum, LLB.AccessionNum, PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum and PFN.SortOrder = 1
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.FaxResult = 'Y' AND LM.FaxStatus = 'S'  
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
ORDER BY LLB.VerifyDate,VM.LASTNAME, VM.FirstName,VM.MiddleName
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SetlstUnSent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SetlstUnSent]

@VerifyDate varchar(10),
@SearchKey varchar(50)
AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.FaxResult,
	CLS.LabSection, CLE.LabExam, LM.QueueTag,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, LLB.RequestNum, 
	LLB.AccessionNum, PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum and PFN.SortOrder = 1
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.FaxResult = 'Y' AND LM.FaxStatus = 'U'
	AND (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.999') 	 
	AND (VM.LastName LIKE @SearchKey +'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
ORDER BY VM.LastName, VM.FirstName, VM.MiddleName, LLB.VerifyDate

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_SetlstUnSentAll]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_SetlstUnSentAll]

@SearchKey varchar(50)
AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.FaxResult,
	CLS.LabSection, CLE.LabExam, LM.QueueTag,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	convert(varchar(10),LLB.VerifyDate,101) VerifyDate, 
	LLB.AccessionNum, LLB.RequestNum, PFN.FaxNumber
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum and PFN.SortOrder = 1
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.FaxResult = 'Y' AND LM.FaxStatus = 'U'
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
	AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate, VM.LastName, VM.FirstName, VM.MiddleName
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_UpdateFaxNumber]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_UpdateFaxNumber]

@HospNum varchar(10),
@FaxNumber varchar(100),
@SortOrder int

AS

update Patient_data..tbPatientFaxNumber
set FaxNumber = @FaxNumber
where HospNum = @HospNum and SortOrder = @SortOrder
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_UpdateFaxStatus]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendFax_UpdateFaxStatus]
@RequestNum varchar(30)
AS

declare @faxnumber as varchar(50)

UPDATE Laboratory..tbLabMaster
SET FaxStatus = 'S'
WHERE RequestNum LIKE @RequestNum

SELECT top 1 @faxnumber = PFN.FaxNumber
FROM Patient_Data..tbPatientFaxNumber PFN
WHERE PFN.HospNum in (select HospNum from tbLABMaster where RequestNum = @RequestNum)

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'FAX SENT - ' + @RequestNum + ' ^ ' +@faxnumber);


GO
/****** Object:  StoredProcedure [dbo].[Lab_SendFax_UpdateSettings]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendFax_UpdateSettings]

@FaxPath varchar(100),
@LocalAccessNum int,
@FaxWaitingTime int,
@FaxResendTime int,
@FaxRefreshTime int,
@FaxUnchargedTime int

AS

UPDATE Patient_Data..tbHospitalInfo
SET FaxPath = @FaxPath, LocalAccessNum = @LocalAccessNum,
	FaxWaitingTime = @FaxWaitingTime, FaxResendTime = @FaxResendTime,
	FaxRefreshTime = @FaxRefreshTime, FaxUnchargedTime = @FaxUnchargedTime
WHERE HospitalID = 1
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Lab_SendMail]  
@VerifyDate varchar(10),  
@VerifyDateEnd varchar(10)= ''  
as  
  
  
IF @VerifyDateEnd = ''   
 BEGIN  
  SET @VerifyDateEnd = @VerifyDate  
 END  
  
SELECT LM.RequestNum, LM.RefNum, LM.UserID, LM.HospNum,  
  LM.EmailResult, LM.QueueTag, LM.SectionID,  
  CLS.LabSection, CLE.LabExam, LLB.AccessionNum,  
  VM.LASTNAME,  
  VM.FirstName,   
  VM.MiddleName,   
  LLB.VerifyDate, PEA.EmailAddress  
FROM Laboratory..tbLabLogBook LLB  
  LEFT OUTER JOIN Laboratory..tbLabMaster LM  ON LM.RequestNum = LLB.RequestNum  
  LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID  
  LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID  
  LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum  
  LEFT OUTER JOIN dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum  
WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDateEnd + ' 23:59:59.99')   
  AND LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' and (LM.EmailStatus is null OR LM.EmailStatus = 'P')  
  AND (LM.QueueTag is null OR  LM.QueueTag = 'L') AND LM.ITEMID <> '198'  
ORDER BY LLB.VerifyDate  
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_AddRecipient]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_AddRecipient] 

@Contact varchar(70),
@EmailAdd varchar(70)

AS

INSERT INTO Laboratory..tbLab_EmailAddresses (Contact, EmailAdd)
VALUES (@Contact, @EmailAdd)




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_DeleteEmailAddress]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_DeleteEmailAddress]

@HospNum varchar(10),
@SortOrder int

AS

delete Patient_data..tbPatientEmailAddresses
where HospNum = @HospNum and SortOrder = @SortOrder
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_DeleteRecipient]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_DeleteRecipient] 

@Contact varchar(70),
@EmailAdd varchar(70)

AS

DELETE FROM tbLab_EmailAddresses
WHERE Contact = @Contact AND EmailAdd = @EmailAdd




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_GetEmailAddress]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_GetEmailAddress]

@HospNum varchar(10)
AS

SELECT PEA.EmailAddress, PEA.SortOrder, PEA.HospNum, 
	isnull(M.LastName,B.LastName) as LastName, 
	isnull(M.FirstName,B.FirstName) as FirstName, 
	isnull(M.MiddleName,B.MiddleName) as MiddleName 
FROM Patient_Data..tbPatientEmailAddresses PEA
	LEFT OUTER JOIN Patient_Data..tbMaster M on M.HospNum = PEA.HospNum
	LEFT OUTER JOIN Patient_Data..tbCashPatient B ON PEA.HospNum=B.HospNum
WHERE PEA.HospNum = @HospNum or M.LastName like @HospNum + '%' and PEA.EmailAddress <> '' 

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_InsertEmailAddress]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_InsertEmailAddress] 

@HospNum varchar(10),
@EmailAddress varchar(100),
@SortOrder int

AS

insert into Patient_data..tbPatientEmailAddresses (HospNum, EmailAddress, SortOrder)
values (@HospNum, @EmailAddress, @SortOrder)
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_MarkUnsent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[Lab_SendMail_MarkUnsent]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET EmailStatus = 'U'
WHERE RequestNum LIKE @RequestNum

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'EMAIL MARK UNSENT - ' + @RequestNum);

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_MarkUnsent_Group]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendMail_MarkUnsent_Group]
@VerifyDate varchar(10)
AS

UPDATE Laboratory..tbLabMaster
SET EmailStatus = 'U'
from Laboratory..tbLabMaster LM
LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
where convert(varchar(10),LLB.VerifyDate,101) = @VerifyDate and EmailStatus is null

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'EMAIL MARK UNSENT GRP - ' + @VerifyDate);

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_MarkUnsentOldResult]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_MarkUnsentOldResult]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabOldResultLogBook
SET EmailStatus = 'U'
WHERE RequestNum LIKE @RequestNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_OldResults]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[Lab_SendMail_OldResults]
AS

SELECT LOL.TransDate, LM.RequestNum, LM.RefNum, LM.HospNum, LLB.AccessionNum, LLB.VerifyDate,
	(VM.LastName + ', ' + VM.FirstName + ' ' + Substring(VM.MiddleName,1,1) + '.')as PatientName,
	CLS.LabSection, CLE.LabExam, LOL.EmailResult, LOL.UserID	
FROM Laboratory..tbLabOldResultLogBook LOL
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LLB.RequestNum = LOL.RequestNum
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LOL.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
WHERE LOL.EmailResult = 'Y' and LOL.EmailStatus is null AND LM.ItemId <> '198'
ORDER BY LOL.TransDate, LM.RequestNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_PauseSending]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendMail_PauseSending]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET EmailStatus = 'P'
WHERE RequestNum LIKE @RequestNum

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'EMAIL PAUSE SENDING - ' + @RequestNum);
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_ReportStatus]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_ReportStatus]
@StartDate as varchar(10),
@EndDate as varchar(10),
@Status as varchar(1)

AS

select LM.HospNum, LM.Requestnum, LM.IdNum, LM.QueueTag,
	LM.RefNum, LM.Transdate as DateCharged, 
	VM.LastName, VM.Firstname, VM.MiddleName, 
	E.LabExam, S.LabSection, LB.verifyDate
from LABORATORY..tblablogbook LB
	left outer join tblabMaster LM on LM.requestnum = LB.requestNum	
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
	left outer join BUILD_FILE..tbCoLabExam E on LM.ItemID = E.LabExamID
	left outer join BUILD_FILE..tbColabSection S on LM.SectionID = S.LabSectionID
where LM.EmailStatus = @Status and (LB.Verifydate between @StartDate and @EndDate + ' 23:59:59.99')
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L')and EmailResult='Y'

order by LB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_ReportStatus_PerSection]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_ReportStatus_PerSection]
	@StartDate as varchar(10),
	@EndDate as varchar(10),
	@Status as varchar(1),
	@SectionID as varchar(3)
AS

SELECT	LM.HospNum, 
		LM.Requestnum,
		LM.IdNum, 
		LM.QueueTag,
		LM.RefNum, 
		LM.Transdate as DateCharged, 
		VM.LastName, 
		VM.Firstname, 
		VM.MiddleName, 
		E.LabExam, 
		S.LabSection, 
		LB.verifyDate
FROM	LABORATORY..tblablogbook LB
		left outer join tblabMaster LM on LM.requestnum = LB.requestNum	
		left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
		left outer join BUILD_FILE..tbCoLabExam E on LM.ItemID = E.LabExamID
		left outer join BUILD_FILE..tbColabSection S on LM.SectionID = S.LabSectionID
WHERE	LM.EmailStatus = @Status and (LB.Verifydate between @StartDate and @EndDate + ' 23:59:59.99')
		AND (LM.QueueTag is null OR  LM.QueueTag = 'L')and EmailResult='Y' and LM.SectionId = @SectionID
ORDER BY LB.VerifyDate

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_Resend]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[Lab_SendMail_Resend]
@RequestNum varchar(30)
AS

SELECT LM.RequestNum, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	PEA.EmailAddress, LLB.VerifyDate, CLS.LabSection, CLE.LabExam, LM.HospNum
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestNum = @RequestNum and LM.RequestStatus = 'W'
	and  LM.EmailResult = 'Y' and LM.EmailStatus = 'S' AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_Resend_Unsent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_Resend_Unsent]
@RequestNum varchar(30)
AS

SELECT LM.RequestNum, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	PEA.EmailAddress, LLB.VerifyDate, CLS.LabSection, CLE.LabExam, LM.HospNum
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' and LM.RequestNum = @RequestNum and  LM.EmailResult = 'Y' and LM.EmailStatus = 'U'
	AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_ResumeSending]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendMail_ResumeSending]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabMaster
SET EmailStatus = null
WHERE RequestNum LIKE @RequestNum


insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'RESUME EMAIL - ' + @RequestNum);
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SearchChargeSlip]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SearchChargeSlip]

@RefNum varchar(30)

AS

select top 1 HospNum from Laboratory..tbLabMaster where RefNum = @RefNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SearchPatient]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SearchPatient] 

@LastName varchar(50)

AS

select HospNum,
       LastName,
       FirstName,
       MiddleName
from dbo.vwPatientResultEntry
where LastName like @LastName + '%'
order by LastName, FirstName, MiddleName


GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetfgridAddRec]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_SetfgridAddRec] AS

SELECT * FROM Laboratory..tbLab_EmailAddresses EA
ORDER BY EA.Contact




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetfgridMsgSent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_SetfgridMsgSent]

@VerifyDate varchar(10)
AS

SELECT LM.HospNum, CLS.LabSection, CLE.LabExam, 
	--M.LastName, M.FirstName, M.MiddleName, 
	CASE WHEN LLB.IdNum LIKE 'M%%' THEN CP.LastName ELSE M.LastName END AS [LASTNAME],
	CASE WHEN LLB.IdNum LIKE 'M%%' THEN CP.FirstName ELSE M.FirstName END AS [FIRSTNAME], 
	CASE WHEN LLB.IdNum LIKE 'M%%' THEN CP.LastName ELSE M.MiddleName END AS [MIDDLENAME],  
	LLB.VerifyDate
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Patient_Data..tbMaster M ON LM.HospNum = M.HospNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Patient_Data..tbCashPatient CP ON LM.HospNum = CP.HospNum
WHERE LM.RequestStatus = 'W' AND convert(varchar(10),LLB.VerifyDate,101) = @VerifyDate AND LM.EmailResult = 'Y' and LM.EmailStatus = 'S'
	AND LM.ItemId <> '198'
ORDER BY LLB.VerifyDate




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetfgridQueue]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SetfgridQueue]

@VerifyDate varchar(10)
AS

SELECT LM.HospNum, LM.EmailStatus, LM.RequestNum,
	LM.RefNum, LM.SectionID, LM.QueueTag, LM.SpecimenID,
	CLE.LabExam, CLS.LabSection, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, LLB.AccessionNum
FROM Laboratory..tbLabLogBook LLB 
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') AND
	LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' AND
	(LM.EmailStatus is null OR LM.EmailStatus = 'P') AND
	(LM.QueueTag is null OR  LM.QueueTag = 'L') AND LM.ItemId <> '198'
	and (select top 1 Isnull(EmailAdd,'') as EmailAdd from tblabemail where Refnum = LM.Refnum) <> ''
ORDER BY LLB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetlstSent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[Lab_SendMail_SetlstSent]

@VerifyDate varchar(10),
@SearchKey varchar(50),
@VerifyDateEnd varchar(10) = @VerifyDate
AS

SELECT LM.HospNum, LM.UserID, LM.EmailResult,
	CLS.LabSection, CLE.LabExam, LLB.AccessionNum,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate, LLB.RequestNum
FROM Laboratory..tbLabMaster LM
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' AND LM.EmailStatus = 'S'
	AND (LLB.VerifyDate between @VerifyDate and @VerifyDateEnd + ' 23:59:59.99')
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey)
	AND (LM.QueueTag is null OR  LM.QueueTag = 'L')	AND LM.ItemId <> '198'
ORDER BY VM.LastName, VM.FirstName, VM.MiddleName, LLB.VerifyDate


GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetlstSent_Search]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SetlstSent_Search] 

@VerifyDate varchar(10),
@SearchKey varchar(50)
AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.EmailResult,
	LLB.RequestNum, LLB.AccessionNum,
	CLS.LabSection, CLE.LabExam, LM.QueueTag,	
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	convert(varchar(10),LLB.VerifyDate,101) VerifyDate
FROM Laboratory..tbLabLogBook LLB 
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
WHERE LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' and LM.EmailStatus = 'S'
	AND (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99')
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
--ORDER BY LLB.VerifyDate, VM.LastName, VM.FirstName, VM.MiddleName
ORDER BY  VM.LastName, VM.FirstName, VM.MiddleName,LLB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetlstSent_SearchAll]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SetlstSent_SearchAll]

@SearchKey varchar(50)
AS

SELECT LM.HospNum, LLB.RequestNum, LM.RefNum, LM.EmailResult,
	CLS.LabSection, CLE.LabExam, LLB.AccessionNum,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LM.UserID, LM.QueueTag,
	convert(varchar(10),LLB.VerifyDate,101) VerifyDate
FROM Laboratory..tbLabLogBook LLB 
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' and LM.EmailStatus = 'S'
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
ORDER BY LLB.VerifyDate, LastName, FirstName, MiddleName

GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetlstUnSent]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SetlstUnSent]

@VerifyDate varchar(10),
@SearchKey varchar(50)

AS

SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.EmailResult,
	LLB.RequestNum, LLB.AccessionNum, LM.QueueTag,
	CLS.LabSection, CLE.LabExam, 
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LLB.VerifyDate
FROM Laboratory..tbLabLogBook LLB 
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') 
	AND LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' and LM.EmailStatus = 'U'
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
ORDER BY LLB.VerifyDate
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_SetlstUnSentAll]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_SetlstUnSentAll]

@SearchKey varchar(50)
AS

SELECT LM.HospNum, LLB.RequestNum, LM.RefNum, LM.EmailResult,
	CLS.LabSection, CLE.LabExam, LLB.AccessionNum, LM.QueueTag,
	VM.LASTNAME,
	VM.FirstName, 
	VM.MiddleName, 
	LM.UserID,convert(varchar(10),LLB.VerifyDate,101) VerifyDate
FROM Laboratory..tbLabLogBook LLB 
	LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
	LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
	LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
	left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum
WHERE LM.RequestStatus = 'W' AND LM.EmailResult = 'Y' and LM.EmailStatus = 'U'
	AND (VM.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) 
	AND LM.ItemId <> '198'
	--AND (LM.QueueTag is null OR  LM.QueueTag = 'L')
ORDER BY LLB.VerifyDate, VM.LastName, VM.FirstName, VM.MiddleName
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateEmailAddress]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateEmailAddress]

@HospNum varchar(10),
@EmailAddress varchar(100),
@SortOrder int

AS

update Patient_data..tbPatientEmailAddresses
set EmailAddress = @EmailAddress
where HospNum = @HospNum and SortOrder = @SortOrder
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateEmailSettings]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateEmailSettings]
@EmailWaitingTime varchar(2),
@SMTP_Server varchar(100),
@SenderName varchar(100),
@SenderEmail varchar(100),
@EmailUsername varchar(50),
@EmailPassword varchar(20),
@EmailPath varchar(100)
AS

UPDATE Patient_Data..tbHospitalInfo
SET EmailWaitingTime = @EmailWaitingTime,
	SMTP_Server = @SMTP_Server,
	SenderName = @SenderName,
	SenderEmail = @SenderEmail,
	EmailUsername = @EmailUsername,
	EmailPassword = @EmailPassword,
	EmailPath = @EmailPath
WHERE HospitalID = 1
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateEmailStatus]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateEmailStatus]
@RequestNum varchar(30)
AS

Declare	@emailaddress varchar(50)

UPDATE Laboratory..tbLabMaster
SET EmailStatus = 'S'
WHERE RequestNum LIKE @RequestNum


SELECT top 1  @emailaddress = PEA.EmailAddress 
FROM Patient_Data..tbPatientEmailAddresses PEA
WHERE PEA.HospNum IN (SELECT HospNum FROM tbLABMaster WHERE RequestNum = @RequestNum)

insert into tbLabAuditTrail (TransDate, UserID, RevenueID, NTUserLogIn, Remarks)
values (getdate(), '', 'LB', HOST_NAME(),'EMAIL SENT - ' + @RequestNum + ' ^ ' + @emailaddress);
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateOldES]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateOldES]
@RequestNum varchar(30)
AS

UPDATE Laboratory..tbLabOldResultLogBook
SET EmailStatus = 'S'
WHERE RequestNum LIKE @RequestNum
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdatePreferences]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_UpdatePreferences]
	@SMTP_Server varchar(100) = '',
	@SenderName varchar(100) = '',
	@SenderEmail varchar(100) = '',
	@EmailPath varchar(100) = '',
	@DefaultMessage text = '',
	@EmailWaitingTime int = 0,
	@EmailResendTime int = 0,
	@EmailRefreshTime int = 0,
	@EmailUnchargedTime int = 0,	
	@EmailUsername varchar(50) = '',
	@EmailPassword varchar(20) = ''
AS

UPDATE Patient_Data..tbHospitalInfo
SET SMTP_Server = @SMTP_Server,
	SenderName = @SenderName,
	SenderEmail = @SenderEmail,
	EmailPath = @EmailPath,
	DefaultMessage = @DefaultMessage,
	EmailWaitingTime = @EmailWaitingTime,
	EmailResendTime = @EmailResendTime,
	EmailRefreshTime = @EmailRefreshTime,
	EmailUnchargedTime = @EmailUnchargedTime,
	EmailUsername = @EmailUsername,
	EmailPassword = @EmailPassword
WHERE HospitalID = 1
GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateRecipient]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateRecipient] 

@Contact varchar(70),
@EmailAdd varchar(70),
@NewContact varchar(70),
@NewEmailAdd varchar(70)

AS

UPDATE tbLab_EmailAddresses
SET Contact = @NewContact, EmailAdd = @NewEmailAdd
WHERE Contact = @Contact AND EmailAdd = @EmailAdd




GO
/****** Object:  StoredProcedure [dbo].[Lab_SendMail_UpdateSettings]    Script Date: 4/13/2018 4:08:35 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Lab_SendMail_UpdateSettings]
	@EmailWaitingTime int = 0,
	@EmailResendTime int = 0,
	@EmailRefreshTime int = 0,
	@EmailUnchargedTime int = 0,
	@EmailPath varchar(100) = '',
	@SMTP_Server varchar(100) = '',
	@SenderName varchar(100) = '',
	@SenderEmail varchar(100) = '',
	@EmailUsername varchar(50) = '',
	@EmailPassword varchar(20) = '',	
	@Mode int = 0
AS

if @Mode = 0
	begin
		UPDATE Patient_Data..tbHospitalInfo
		SET SMTP_Server = @SMTP_Server,
			SenderName = @SenderName,
			SenderEmail = @SenderEmail,
			EmailUsername = @EmailUsername,
			EmailPassword = @EmailPassword
		WHERE HospitalID = 1
	end
else
	begin
		UPDATE Patient_Data..tbHospitalInfo
		SET EmailWaitingTime = @EmailWaitingTime,
			EmailResendTime = @EmailResendTime,
			EmailRefreshTime = @EmailRefreshTime,
			EmailUnchargedTime = @EmailUnchargedTime,
			EmailPath = @EmailPath
		WHERE HospitalID = 1
	end
GO



CREATE PROCEDURE [dbo].[Lab_FaxEMail_GetOldResult] 
@Mode int

AS

if @Mode = 0 --Fax
	Begin
		Select	LM.RequestNum, 
				LM.RefNum, 
				LM.HospNum, 
				LM.UserID, 
				LM.FaxResult,
				CLS.LabSection, 
				CLE.LabExam,
				LLB.VerifyDate, 
				LLB.AccessionNum,
				VM.LASTNAME,
				VM.FirstName, 
				VM.MiddleName, 	
				PFN.FaxNumber
		From	Laboratory..tbLabLogbook LLB 
				LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
				LEFT OUTER JOIN Patient_Data..tbPatientFaxNumber PFN ON LM.HospNum = PFN.HospNum
				LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
				LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
				LEFT OUTER JOIN dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum	
		Where	LM.OldFaxResult = 'Y' 
				AND LM.RequestStatus = 'W' 
				AND isdate(LLB.VerifyDate) = 1
				AND LM.ItemId <> '198'
	End
else if @Mode = 1 --Email
	Begin
		Select	LM.RequestNum, 
				LM.RefNum, 
				LM.HospNum, 
				LM.UserID, 
				LM.EmailResult,
				CLS.LabSection, 
				CLE.LabExam,
				LLB.VerifyDate, 
				LLB.AccessionNum,
				VM.LASTNAME,
				VM.FirstName, 
				VM.MiddleName		
		From	Laboratory..tbLabLogbook LLB
				LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum
				LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID
				LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID
				LEFT OUTER JOIN dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum	
		Where	OldEmailResult = 'Y' and LM.RequestStatus = 'W' and isdate(LLB.VerifyDate) = 1
				AND LM.ItemId <> '198'
	End
GO



CREATE PROCEDURE [dbo].[Lab_FaxEmailCodes] 
	@mode as int
AS

BEGIN

SET NOCOUNT ON;

IF @mode = 1	--Fax Codes
	BEGIN
		SELECT  LabExamID, LabExam
		FROM    Build_File.dbo.tbCoLabExam
		WHERE   (LabExamID IN ('1469', '2060', '2061', '2062', '2063')) and [Status] = 'A'
	END
ELSE IF @mode=2	--Email Codes
	BEGIN
		SELECT	LabExamID, LabExam
		FROM    Build_File.dbo.tbCoLabExam
		WHERE   (LabExamID IN ('1530', '2070')) and [Status] = 'A'
	END
END

GO


CREATE PROCEDURE [dbo].[Lab_FaxEmail_isBatchResult]
@RefNum varchar(30),
@SectionID varchar(3),
@AccessionNum varchar(15)

AS

SELECT	DISTINCT 
		LM.RequestNum, 
		LM.RefNum, 
		LM.SectionID, 
		V.Code, 
		isnull(V.ResultName,V.ChargingName) as ExamName,
		V.formType, 
		V.Valuetype
FROM	Laboratory..tbLabMaster LM
		LEFT OUTER JOIN Laboratory..tbLabLogbook LB on LM.RequestNum = LB.RequestNum
		LEFT OUTER JOIN Build_File..tbCoLabValues V on LM.ItemID = V.Code	
WHERE	LM.Refnum = @RefNum 
		AND ((V.FormType in ('1', '7', 'U', '5') and V.ValueType <> '3') or isnull(LM.ProfileID,'') <> '' or (V.FormType in ('D') and V.ValueType = '5'))
		AND LM.SectionId = ltrim(rtrim(@SectionID)) 
		AND LB.AccessionNum = @AccessionNum 
		AND isnull(LM.QueueTag,'') = ''
ORDER BY LM.RequestNum
GO