USE [Church]
GO
/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsGet]    Script Date: 08/03/2010 09:18:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersPersonsGet]
	@ChurchID int = -1,
	@RegisterTypeID int = -1
AS

	/* SET NOCOUNT ON */
	
DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
		
SELECT 
	CRP.RegisterNo,
	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
	dbo.IsBaptized(P.PersonID) IsBaptized,
	(case when (dbo.IsBaptized(P.PersonID) = 1 AND P.ChurchID = @ChurchID) THEN cast(1 as bit) else cast(0 as bit) END) IsMember,
	(case when T.TransferredFromChurchID IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS IsTransferred,
	CRP.ChurchID,
	CRP.RegisterTypeID,
	CRP.RegisterPersonDetails,
	P.PersonID,
	P.AddressID,
	LastName,
	FirstName,
	MaidenName,
	FatherID,
	MotherID,
	BirthDate,
	(CASE WHEN BirthDate IS NOT NULL THEN 
			(CASE WHEN DeathDate IS NULL THEN dbo.fn_CalculateAge(BirthDate, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate, 
	Details,
	-- P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName,
	A.Address,
	D.Name District,
	B.BaptismDate

FROM ChurchesRegistersPersons AS CRP
	INNER JOIN Persons P ON (CRP.PersonID = P.PersonID)
		LEFT OUTER JOIN (SELECT T1.PersonID, T1.TransferDate, T1.TransferredFromChurchID FROM Transfers T1
			INNER JOIN (SELECT PersonID, MAX(TransferDate) TransferDate FROM Transfers 
				WHERE TransferredFromChurchID = @ChurchID OR TransferredToChurchID = @ChurchID GROUP BY PersonID) TL 
				ON (T1.PersonID = TL.PersonID AND T1.TransferDate = TL.TransferDate)) T 
				ON (P.PersonID = T.PersonID AND T.TransferredFromChurchID = @ChurchID)
	LEFT OUTER JOIN Addresses A ON P.AddressID = A.AddressID
		LEFT OUTER JOIN Districts D ON A.DistrictID = D.DistrictID
	LEFT OUTER JOIN Baptisms B ON P.PersonID = B.PersonID

WHERE CRP.ChurchID = @ChurchID AND CRP.RegisterTypeID = @RegisterTypeID
	--((EXISTS(SELECT * FROM Transfers WHERE PersonID = P.PersonID)  OR )
ORDER BY CRP.RegisterNo
