USE [Church]
GO

/*****************************************************************************************************/
/******         List of tables which differ between version 1.0.0.6 and version 1.0.0.5			******/
/****** 				ChurchesRegistersFamilies												******/
/****** 				ChurchesRegistersPersons												******/
/*****************************************************************************************************/

/****** ChurchesRegistersFamilies ******/
ALTER TABLE ChurchesRegistersFamilies 
ADD ActiveDate datetime
GO

ALTER TABLE ChurchesRegistersFamilies 
ADD InactiveDate datetime
GO


/****** ChurchesRegistersPersons ******/
ALTER TABLE ChurchesRegistersPersons 
ADD ActiveDate datetime
GO

ALTER TABLE ChurchesRegistersPersons 
ADD InactiveDate datetime
GO


/*****************************************************************************************************/
/******         List of procedures which differ between version 1.0.0.6 and version 1.0.0.5		******/
/****** 				ChildrenByPersonGet														******/
/****** 				ChildrenByMariageGet													******/
/****** 				BlessedByMariageGet														******/
/****** 				BaptizedByMariageGet													******/
/****** 				HolySpiritBaptizedByMariageGet											******/
/****** 				MembersByMariageGet														******/
/****** 				ChurchChildrenGet														******/
/****** 				ChurchRegistersFamiliesGet												******/
/****** 				ChurchRegistersFamiliesSet												******/
/****** 				ChurchRegistersPersonsGet												******/
/****** 				ChurchRegistersPersonsSet												******/
/****** 				PersonsGet																******/
/****** 				ChurchGroupsPersonsGet													******/
/*****************************************************************************************************/


/****** Object:  StoredProcedure [dbo].[ChildrenByPersonGet]    Script Date: 07/30/2011 22:22:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChildrenByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
	
DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		PersonID,
		AddressID,
		LastName,
		FirstName,
		MaidenName,
		FatherID,
		MotherID,
		BirthDate,
		(CASE WHEN BirthDate IS NOT NULL THEN 
			(CASE WHEN DeathDate IS NULL THEN dbo.fn_CalculateAge(BirthDate, @currentDate) 
				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 C.Name END) AS ChurchName,
		P.BelongDate
FROM Persons P
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
WHERE (FatherID = @PersonID OR MotherID = @PersonID)
ORDER BY P.BirthDate ASC
GO

/****** Object:  StoredProcedure [dbo].[ChildrenByMariageGet]    Script Date: 07/30/2011 23:12:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChildrenByMariageGet]
	@MarriageID	int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName,
		P.BelongDate

FROM Marriages M
	INNER JOIN Persons P ON (P.FatherID = M.HusbandID OR P.MotherID = M.WifeID )
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
		
WHERE
	MarriageID = @MarriageID
ORDER BY P.BirthDate ASC
GO

/****** Object:  StoredProcedure [dbo].[BlessedByMariageGet]    Script Date: 07/31/2011 00:02:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BlessedByMariageGet]
	@MarriageID	int = -1,
	@category	int = 1
AS


DECLARE @currentDate datetime
SET @currentDate = GETDATE()

/* SET NOCOUNT ON */
SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		--P.Photo,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		P.ChurchName,
		P.BelongDate,
		B.BlessingDate,
		B.BlessingChurch,
		B.BlessingServant
FROM Marriages M
	INNER JOIN Persons P ON
		P.FatherID = CASE WHEN @category = 1 OR @category = 3 THEN M.HusbandID ELSE -1 END OR
		P.MotherID = CASE WHEN @category = 1 OR @category = 3 THEN M.WifeID ELSE -1 END OR
		M.WifeID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END OR
		M.HusbandID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END 
	--INNER JOIN Persons P ON (P.FatherID = M.HusbandID OR P.MotherID = M.WifeID 
	--	OR M.WifeID = P.PersonID OR M.HusbandID = P.PersonID)
	INNER JOIN Blessings B ON (B.PersonID = P.PersonID)	
WHERE
	MarriageID = @MarriageID
ORDER BY P.BirthDate ASC
GO


/****** Object:  StoredProcedure [dbo].[BaptizedByMariageGet]    Script Date: 07/31/2011 00:15:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptizedByMariageGet]
	@MarriageID	int = -1,
	@category	int = 1
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

/* SET NOCOUNT ON */
SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		--P.Photo,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		P.BelongDate,
		B.BaptismDate,
		B.BaptismChurch,
		B.BaptismServant
FROM Marriages M
	--	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
	INNER JOIN Persons P ON
		P.FatherID = CASE WHEN @category = 1 OR @category = 3 THEN M.HusbandID ELSE -1 END OR
		P.MotherID = CASE WHEN @category = 1 OR @category = 3 THEN M.WifeID ELSE -1 END OR
		M.WifeID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END OR
		M.HusbandID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END 
		 --P ON ( P.FatherID = M.HusbandID OR P.MotherID = M.WifeID 
			--					OR M.WifeID = P.PersonID OR M.HusbandID = P.PersonID )
	INNER JOIN Baptisms B ON (B.PersonID = P.PersonID)	
WHERE
	MarriageID = @MarriageID
ORDER BY P.BirthDate ASC
GO

/****** Object:  StoredProcedure [dbo].[HolySpiritBaptizedByMariageGet]    Script Date: 07/30/2011 23:30:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritBaptizedByMariageGet]
	@MarriageID	int = -1,
	@category	int = 1
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()


/* SET NOCOUNT ON */
SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		--P.Photo,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C1.Name END) AS ChurchName,
		P.BelongDate,
		HSB.HolySpiritBaptismDate,
		HSB.HolySpiritBaptismAddress
FROM Marriages M
	INNER JOIN Persons P ON
		P.FatherID = CASE WHEN @category = 1 OR @category = 3 THEN M.HusbandID ELSE -1 END OR
		P.MotherID = CASE WHEN @category = 1 OR @category = 3 THEN M.WifeID ELSE -1 END OR
		M.WifeID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END OR
		M.HusbandID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END 
	--INNER JOIN Persons P ON (P.FatherID = M.HusbandID OR P.MotherID = M.WifeID 
	--	OR M.WifeID = P.PersonID OR M.HusbandID = P.PersonID)
	INNER JOIN HolySpiritBaptisms HSB ON (HSB.PersonID = P.PersonID)	
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE
	MarriageID = @MarriageID
ORDER BY P.BirthDate ASC
GO


/****** Object:  StoredProcedure [dbo].[MembersByMariageGet]    Script Date: 07/30/2011 23:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MembersByMariageGet]
	@MarriageID	int = -1,
	@category	int = 1
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

/* SET NOCOUNT ON */
SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		--P.Photo,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		--P.BelongDate,	
		(case when M1.ChurchID IS NULL THEN M1.Church else C1.Name END) AS Church,
		M1.IsActive,
		M1.BecomeDate,
		M1.RetireDate,
		M1.Details MemberDetails
FROM Marriages M
	INNER JOIN Persons P ON
		P.FatherID = CASE WHEN @category = 1 OR @category = 3 THEN M.HusbandID ELSE -1 END OR
		P.MotherID = CASE WHEN @category = 1 OR @category = 3 THEN M.WifeID ELSE -1 END OR
		M.WifeID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END OR
		M.HusbandID = CASE WHEN @category = 1 OR @category = 2 THEN P.PersonID ELSE -1 END 
	INNER JOIN Members M1 ON (M1.PersonID = P.PersonID)	
		LEFT OUTER JOIN Churches C1 ON (M1.ChurchID = C1.ChurchID)
WHERE
	MarriageID = @MarriageID
ORDER BY P.BirthDate ASC
GO

/****** Object:  StoredProcedure [dbo].[ChurchRegistersFamiliesSet]    Script Date: 01/01/2011 02:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersFamiliesSet]
	@Operation				int = -1,
	@ChurchRegisterFamilyID int OUTPUT,
	@ChurchID				int,
	@RegisterTypeID			int,
	@MarriageID				int,
	@IsActive				bit,
	@RegisterNo				int,
	@ActiveDate				datetime,
	@InactiveDate			datetime,
	@RegisterFamilyDetails	nvarchar(256)
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


-- check parameters
/*
IF @Operation IN (0, 1) AND EXISTS(SELECT * FROM ChurchesGroupsPersons 
	WHERE [PersonID] = @PersonID AND ChurchGroupChurchGroupPersonID <> @ChurchGroupChurchGroupPersonID)
BEGIN
	SET @ErrorCode = 1
	GOTO __END
END
*/

IF @Operation IN (1, 2) AND NOT EXISTS(SELECT * FROM ChurchesRegistersFamilies 
		WHERE ChurchRegisterFamilyID = @ChurchRegisterFamilyID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END

IF @Operation = 0
BEGIN 
	INSERT ChurchesRegistersFamilies (IsActive, ChurchID, RegisterTypeID, MarriageID, RegisterNo, ActiveDate, InactiveDate, RegisterFamilyDetails)
	VALUES(@IsActive, @ChurchID, @RegisterTypeID, @MarriageID, @RegisterNo, @ActiveDate, @InactiveDate, @RegisterFamilyDetails)

	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 3
		GOTO __END
	END
	
	SET @ChurchRegisterFamilyID = @@IDENTITY
END

IF @Operation = 1
BEGIN
	UPDATE ChurchesRegistersFamilies
	SET
		IsActive = @IsActive,
		RegisterNo = @RegisterNo,
		ActiveDate = @ActiveDate, 
		InactiveDate = @InactiveDate,
		RegisterFamilyDetails = @RegisterFamilyDetails
	WHERE ChurchRegisterFamilyID = @ChurchRegisterFamilyID
	
	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 4
		GOTO __END
	END
END	

IF @Operation = 2
BEGIN
	DELETE ChurchesRegistersFamilies
	WHERE ChurchRegisterFamilyID = @ChurchRegisterFamilyID

	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 5
		GOTO __END
	END
END	

__END:
IF @ErrorCode <> 0
BEGIN
	DECLARE @ErrorMessage varchar(128)
	SELECT @ErrorMessage = 'ErrorCode = ' + CONVERT(nvarchar, @ErrorCode)
	RAISERROR(@ErrorMessage, 16, @ErrorCode)
END

RETURN
GO


/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsSet]    Script Date: 01/01/2011 02:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersPersonsSet]
	@Operation				int = -1,
	@ChurchRegisterPersonID int OUTPUT,
	@ChurchID				int,
	@RegisterTypeID			int,
	@PersonID				int,
	@IsActive				bit,
	@RegisterNo				int,
	@ActiveDate				datetime, 
	@InactiveDate			datetime,
	@RegisterPersonDetails	nvarchar(256)
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


-- check parameters
/*
IF @Operation IN (0, 1) AND EXISTS(SELECT * FROM ChurchesGroupsPersons 
	WHERE [PersonID] = @PersonID AND ChurchGroupChurchGroupPersonID <> @ChurchGroupChurchGroupPersonID)
BEGIN
	SET @ErrorCode = 1
	GOTO __END
END
*/

IF @Operation IN (1, 2) AND NOT EXISTS(SELECT * FROM ChurchesRegistersPersons 
		WHERE ChurchRegisterPersonID  = @ChurchRegisterPersonID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END

IF @Operation = 0
BEGIN 
	INSERT ChurchesRegistersPersons (IsActive, ChurchID, RegisterTypeID, PersonID, RegisterNo, ActiveDate, InactiveDate, RegisterPersonDetails)
	VALUES(@IsActive, @ChurchID, @RegisterTypeID, @PersonID, @RegisterNo, @ActiveDate, @InactiveDate, @RegisterPersonDetails)

	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 3
		GOTO __END
	END
	
	SET @ChurchRegisterPersonID = @@IDENTITY
END

IF @Operation = 1
BEGIN
	UPDATE ChurchesRegistersPersons
	SET 
		IsActive = @IsActive,
		RegisterNo = @RegisterNo,
		ActiveDate = @ActiveDate, 
		InactiveDate = @InactiveDate,
		RegisterPersonDetails = @RegisterPersonDetails
	WHERE ChurchRegisterPersonID = @ChurchRegisterPersonID 
	
	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 4
		GOTO __END
	END
END	

IF @Operation = 2
BEGIN
	DELETE ChurchesRegistersPersons
	WHERE ChurchRegisterPersonID = @ChurchRegisterPersonID 

	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 5
		GOTO __END
	END
END	

__END:
IF @ErrorCode <> 0
BEGIN
	DECLARE @ErrorMessage varchar(128)
	SELECT @ErrorMessage = 'ErrorCode = ' + CONVERT(nvarchar, @ErrorCode)
	RAISERROR(@ErrorMessage, 16, @ErrorCode)
END

RETURN
GO


/****** Object:  StoredProcedure [dbo].[ChurchRegistersFamiliesGet]    Script Date: 01/01/2011 02:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersFamiliesGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	
DECLARE @currentDate datetime
SET @currentDate = GETDATE()

	
	SELECT  
		CRF.ChurchRegisterFamilyID,
		CRF.RegisterNo,
		CRF.ChurchID,
		CRF.RegisterTypeID,
		CRF.MarriageID,
		CRF.IsActive,

		M.Divorce,
		
		(CASE WHEN (PH.LastName IS NULL) THEN PW.LastName ELSE PH.LastName END) + ' ' + -- use COALESCE 
		(CASE WHEN PH.FirstName IS NULL THEN '' ELSE PH.FirstName END) + ' ' + 
		(CASE WHEN PH.FirstName IS NOT NULL AND PW.FirstName IS NOT NULL THEN 'si ' ELSE '' END) + 
		(CASE WHEN PW.FirstName IS NULL THEN '' ELSE PW.FirstName END) FamilyName, 

		M.HusbandID HusbandID, 
		M.WifeID WifeID, 

		(CASE WHEN PH.LastName IS NULL THEN '' ELSE PH.FirstName + ' ' + PH.LastName END) HusbandName, 
		PH.FirstName HusbandFirstName, 
		PH.LastName HusbandLastName, 
		PH.BirthDate HusbandBirthDate,
		(CASE WHEN PH.BirthDate IS NOT NULL THEN 
			(CASE WHEN PH.DeathDate IS NULL THEN dbo.fn_CalculateAge(PH.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(PH.BirthDate, PH.DeathDate) END) END) HusbandAges,

		(CASE WHEN PW.LastName IS NULL THEN '' ELSE PW.FirstName + ' ' + PW.LastName END) WifeName,
		PW.FirstName WifeFirstName, 
		PW.LastName WifeLastName, 
		PW.BirthDate WifeBirthDate,
		(CASE WHEN PW.BirthDate IS NOT NULL THEN 
			(CASE WHEN PW.DeathDate IS NULL THEN dbo.fn_CalculateAge(PW.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(PW.BirthDate, PW.DeathDate) END) END) WifeAges,
				
		M.CivilServiceDate, 
		M.ReligiousServiceDate,
		M.WeddingChurchID,
		(case when M.WeddingChurchID IS NULL THEN M.WeddingChurch else C.Name END) AS WeddingChurch,
		M.WeddingServants,
		(SELECT count(PersonID) FROM Persons P WHERE M.HusbandID = P.FatherID OR M.WifeID = P.MotherID) ChildrenNo,
		(case when PH.PersonID IS NOT NULL AND PH.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsHusbandAlive,
		(case when PW.PersonID IS NOT NULL AND PW.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsWifeAlive,
		(case when (PH.PersonID IS NOT NULL AND PH.DeathDate IS NULL) OR (PW.PersonID IS NOT NULL AND PW.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) END) IsHusbandOrWifeAlive,
		(case when (PH.PersonID IS NOT NULL AND PH.DeathDate IS NULL) AND (PW.PersonID IS NOT NULL AND PW.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) END) IsHusbandAndWifeAlive,
		PH.DeathDate HusbandDeathDate, 
		PW.DeathDate WifeDeathDate,

		CRF.ActiveDate,
		CRF.InactiveDate,	
		CRF.RegisterFamilyDetails

	FROM Marriages M
		INNER JOIN ChurchesRegistersFamilies CRF ON (CRF.MarriageID = M.MarriageID)
		LEFT OUTER JOIN Churches C ON (M.WeddingChurchID = C.ChurchID)
		LEFT OUTER JOIN Persons PW ON (M.WifeID = PW.PersonID)
		LEFT OUTER JOIN Persons PH ON (M.HusbandID = PH.PersonID)
		--INNER JOIN Persons C ON (M.HusbandID = C.FatherID OR M.WifeID = C.MotherID)
	WHERE (CRF.ChurchID = @ChurchID AND CRF.RegisterTypeID = @RegisterTypeID)
	ORDER BY CRF.RegisterNo
GO


/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsGet]    Script Date: 01/01/2011 02:42:08 ******/
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

DECLARE @currentDate datetime
SET @currentDate = GETDATE()
		
SELECT 
	CRP.ChurchRegisterPersonID,
	CRP.RegisterNo,
	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
	CRP.IsActive,
	(case when B1.BlessingID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsBlessed,
	(case when B2.BaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsBaptized,
	--dbo.IsBaptized(P.PersonID) IsBaptized,
	ISNULL(M.IsActive, 0) AS IsMember,
	--(case when T.TransferredFromChurchID IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS IsTransferred,
	(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
	CRP.ChurchID,
	CRP.RegisterTypeID,

	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, @currentDate) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate, 
	M.Details MemberDetails,
	-- P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName,
	P.BelongDate,
	A.Address,
	D.Name District,
	B1.BlessingDate,
	(case when B1.BlessingChurchID IS NULL THEN B1.BlessingChurch else C1.Name END) AS BlessingChurch,
	B1.BlessingServant,
	B2.BaptismDate,
	(case when B2.BaptismChurchID IS NULL THEN B2.BaptismChurch else C2.Name END) AS BaptismChurch,
	B2.BaptismServant,
	M.BecomeDate MemberBecomeDate,
	(case when M.ChurchID IS NULL THEN M.Church else C3.Name END) AS MemberChurch,
	H.HolySpiritBaptismDate,
	J.Name Job,
	P.Details,
	
	CRP.ActiveDate,
	CRP.InactiveDate,	
	CRP.RegisterPersonDetails
		
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 Blessings B1 ON P.PersonID = B1.PersonID
		LEFT OUTER JOIN Churches C1 ON (B1.BlessingChurchID = C1.ChurchID)
	LEFT OUTER JOIN Baptisms B2 ON P.PersonID = B2.PersonID
		LEFT OUTER JOIN Churches C2 ON (B2.BaptismChurchID = C2.ChurchID)
	LEFT OUTER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1)
		LEFT OUTER JOIN Churches C3 ON (M.ChurchID = C3.ChurchID) 
	LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
	LEFT OUTER JOIN PersonsFinancialPositions PFP ON (P.PersonID = PFP.PersonID)
		LEFT OUTER JOIN Jobs J ON (PFP.JobID = J.JobID)
WHERE CRP.ChurchID = @ChurchID AND CRP.RegisterTypeID = @RegisterTypeID
	--((EXISTS(SELECT * FROM Transfers WHERE PersonID = P.PersonID)  OR )
ORDER BY CRP.RegisterNo
GO



/****** Object:  StoredProcedure [dbo].[ChurchChildrenGet]    Script Date: 01/01/2011 02:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchChildrenGet]
	@ChurchID	int = -1
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		ISNULL(M.IsActive, 0) AS IsMember,
		P.PersonID,
		P.AddressID,
		P.LastName,
		P.FirstName,
		P.MaidenName,
		P.FatherID,
		P.MotherID,
		P.BirthDate,
		(CASE WHEN P.BirthDate IS NOT NULL THEN 
			(CASE WHEN P.DeathDate IS NULL THEN dbo.fn_CalculateAge(P.BirthDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(P.BirthDate, P.DeathDate) END) END) Ages,
		P.PlaceOfBirth,
		P.CNP,
		P.Series,
		P.SeriesNumber,
		P.Sex,
		-- Photo,
		P.HomePhone,
		P.MobilePhone,
		P.DeathDate,
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C1.Name END) AS ChurchName,
		P.BelongDate,
		(CASE WHEN (PF.LastName IS NULL) THEN PM.LastName ELSE PF.LastName END) + ' ' + -- use COALESCE 
		(CASE WHEN PF.FirstName IS NULL THEN '' ELSE PF.FirstName END) + ' ' + 
		(CASE WHEN PF.FirstName IS NOT NULL AND PM.FirstName IS NOT NULL THEN 'si ' ELSE '' END) + 
		(CASE WHEN PM.FirstName IS NULL THEN '' ELSE PM.FirstName END) ParentsName, 

		(CASE WHEN PF.LastName IS NULL THEN '' ELSE PF.FirstName + ' ' + PF.LastName END) FatherName, 
		(CASE WHEN PM.LastName IS NULL THEN '' ELSE PM.FirstName + ' ' + PM.LastName END) MotherName,

		--(case when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) END) IsFatherAlive,
		(case when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NULL) THEN cast(1 as bit)
				when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NOT NULL) THEN cast(0 as bit)
				else null END) IsFatherAlive,
		--(case when (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) END) IsMotherAlive,
		(case when (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit)
				when (PM.PersonID IS NOT NULL AND PM.DeathDate IS NOT NULL) THEN cast(0 as bit) 
				else null END) IsMotherAlive,
		--(case when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NULL) OR (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) END) IsFatheOrMotherAlive,
		(case when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NULL) OR (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit)
				when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NOT NULL) AND (PM.PersonID IS NOT NULL AND PM.DeathDate IS NOT NULL) THEN cast(0 as bit)
				else null END) IsFatherOrMotherAlive,
		(case when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NULL) AND (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit)
				when (PF.PersonID IS NOT NULL AND PF.DeathDate IS NOT NULL) OR (PM.PersonID IS NOT NULL AND PM.DeathDate IS NOT NULL) THEN cast(0 as bit)
				else null END) IsFatherAndMotherAlive,
		PF.DeathDate FatherDeathDate, PM.DeathDate MotherDeathDate	
FROM Persons P
	LEFT OUTER JOIN Persons PF ON (P.FatherID = PF.PersonID)
	LEFT OUTER JOIN Persons PM ON (P.MotherID = PM.PersonID)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
	LEFT OUTER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1) 
WHERE (P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL))
	AND (PM.DeathDate IS NOT NULL OR PF.DeathDate IS NOT NULL)
	-- AND ((PM.PersonID IS NOT NULL AND PM.DeathDate IS NOT NULL) OR (PF.PersonID IS NOT NULL AND PF.DeathDate IS NOT NULL))
GO

/****** Object:  StoredProcedure [dbo].[PersonsGet]    Script Date: 01/01/2011 02:29:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

ALTER PROCEDURE [dbo].[PersonsGet]
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		ISNULL(M.IsActive, 0) AS IsMember,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		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, @currentDate) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		-- Photo,
		HomePhone,
		MobilePhone,
		DeathDate,	
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName,
		P.BelongDate,
		D.Name District, A.Address Address

FROM Persons P
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
	LEFT OUTER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1) 
	LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
	LEFT OUTER JOIN Addresses A ON (P.AddressID = A.AddressID)
    LEFT OUTER JOIN Districts D ON (A.DistrictID = D.DistrictID)
GO


/****** Object:  StoredProcedure [dbo].[ChurchGroupsPersonsGet]    Script Date: 07/10/2011 22:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchGroupsPersonsGet]
	@ChurchID int  = -1,
	@GroupTypeID int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT (case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		ISNULL(M.IsActive, 0) AS IsMember,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		CGP.ChurchGroupPersonID,
		CGP.ChurchID,
		GT.GroupTypeID, 
		GT.Name GroupType,
		CGP.IsActive,
		CGP.ComingIntoGroupDate,
		CGP.LeavingGroupDate,
		CGP.GroupPersonDetails, 
		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, @currentDate) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,  
		P.Details,
		-- P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C1.Name END) AS ChurchName,
		P.BelongDate,
		A.Address,
		D.Name District
FROM Persons P
	INNER JOIN ChurchesGroupsPersons CGP ON CGP.PersonID = P.PersonID
	INNER JOIN GroupsTypes GT ON CGP.GroupTypeID = GT.GroupTypeID
	LEFT OUTER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID) 
	LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
	LEFT OUTER JOIN Addresses A ON P.AddressID = A.AddressID
		LEFT OUTER JOIN Districts D ON A.DistrictID = D.DistrictID
WHERE CGP.ChurchID = @ChurchID AND CGP.GroupTypeID = @GroupTypeID

GO