USE [Church]
GO

/*****************************************************************************************************/
/******         List of new tables on version 1.0.0.7                                           ******/
/****** 				Fonts		               												******/
/*****************************************************************************************************/

/****** Object:  Table [dbo].[Fonts]    Script Date: 04/13/2012 23:42:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Fonts](
	[FontID] [int] IDENTITY(1,1) NOT NULL,
	[FontType] [varchar](50) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Style] [tinyint] NOT NULL,
	[Size] [float] NOT NULL,
	[Color] [int] NOT NULL,
 CONSTRAINT [PK_Fonts] PRIMARY KEY CLUSTERED 
(
	[FontID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/*****************************************************************************************************/
/******         List of tables which differ between version 1.0.0.7 and version 1.0.0.6			******/
/****** 				Marriages               												******/
/****** 				Transfers               												******/
/*****************************************************************************************************/

/****** Marriages ******/
ALTER TABLE Marriages 
ADD DivorceDate datetime
GO

ALTER TABLE Marriages 
ADD Details nvarchar(256)
GO

/****** Transfers ******/
sp_RENAME 'Transfers.TransferredFromChurchID' , 'TransferFromChurchID', 'COLUMN'
GO
sp_RENAME 'Transfers.TransferredFromChurchName' , 'TransferFromChurchName', 'COLUMN'
GO
sp_RENAME 'Transfers.TransferredToChurchID' , 'TransferToChurchID', 'COLUMN'
GO
sp_RENAME 'Transfers.TransferredToChurchName' , 'TransferToChurchName', 'COLUMN'
GO
sp_RENAME 'Transfers.TransferDate' , 'TransferFromDate', 'COLUMN'
GO
sp_RENAME 'Transfers.TransferAct' , 'TransferFromAct', 'COLUMN'
GO

ALTER TABLE Transfers 
ADD TransferToDate datetime
GO

ALTER TABLE Transfers 
ADD TransferToAct nvarchar(50)
GO

UPDATE Transfers
	SET 
		TransferToDate = TransferFromDate,
		TransferToAct = TransferFromAct
GO


/******         Update Persons.BelongDate       ******/
DECLARE @TemporaryPersons TABLE
(
	[PersonID] [int] NOT NULL,
	[ChurchID] [int],
	[BelongDate] [datetime],
	[TransferDate] [datetime]
)

INSERT INTO @TemporaryPersons

SELECT DISTINCT P.PersonID, P.ChurchID, BelongDate, 
	(SELECT MAX(T.TransferFromDate) FROM Transfers T WHERE T.PersonID = P.PersonID AND 
				(((P.ChurchID IS NULL ) AND (T.TransferToChurchID IS NULL)) OR (P.ChurchID = T.TransferToChurchID))) AS TransferDate
FROM Persons P
		INNER JOIN Transfers T2 ON P.PersonID=T2.PersonID
		INNER JOIN Churches C ON T2.TransferFromChurchID=C.ChurchID
WHERE C.IsChild=0

UPDATE Persons
	SET BelongDate = TP.TransferDate
FROM Persons P
	INNER JOIN @TemporaryPersons TP ON P.PersonID = TP.PersonID
WHERE P.BelongDate <> TP.TransferDate

/*****************************************************************************************************/
/******         List of new procedures on version 1.0.0.7										******/
/****** 				MembersByChurchGet2														******/
/****** 				SpecialCasesGet															******/
/****** 				FontsSet															    ******/
/****** 				FontsGet															    ******/
/*****************************************************************************************************/


/****** Object:  StoredProcedure [dbo].[MembersByChurchGet2]    Script Date: 03/31/2012 16:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MembersByChurchGet2]
	@ChurchID	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
	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
	M.IsActive,
	M.MemberID,
	M.ChurchID MemberChurchID,
	(case when M.ChurchID IS NULL THEN M.Church else C3.Name END) AS MemberChurch,
	M.BecomeDate,
	M.RetireDate,
	M.Details MemberDetails, 
	M.PersonID, 
	(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,
	(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
	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, 
	(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,
	H.HolySpiritBaptismDate,
	J.Name Job,
	P.Details
		
FROM Persons P 
	INNER JOIN Members M ON M.PersonID = P.PersonID
		LEFT OUTER JOIN Churches C3 ON (M.ChurchID = C3.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 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 (M.ChurchID = @ChurchID OR @ChurchID = -1)
ORDER BY M.BecomeDate
GO



/****** Object:  StoredProcedure [dbo].[SpecialCasesGet]    Script Date: 03/31/2012 16:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SpecialCasesGet]
	@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, 
		ISNULL(M.IsActive, 0) AS IsMember,
		SC.SpecialCaseID,
		SC.ChurchID SpecialCaseChurchID,  
		SC.IsActive, 
		SC.SpecialCaseDate,
		SC.SolvedDate, 
		SC.SpecialCaseDetails,
		P.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,
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C1.Name END) AS ChurchName,
		P.BelongDate
	FROM Persons P
		INNER JOIN SpecialCases SC ON (P.PersonID = SC.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.PersonID = @PersonID
GO

/****** Object:  StoredProcedure [dbo].[FontsSet]    Script Date: 04/13/2012 23:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FontsSet]
	@Operation		int			= -1,
	@FontID			int			= -1 OUTPUT,
	@FontType		varchar(50),
	@Name			varchar(50),
	@Style			tinyint,
	@Size			float,
	@Color			int
AS

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT INTO Fonts(Name, FontType, Style, Size, Color)
    VALUES (@Name, @FontType, @Style, @Size, @Color)

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

IF @Operation = 1
BEGIN
	UPDATE Fonts
	SET
		Name		= @Name,
		FontType	= @FontType,
		Style		= @Style,
		Size		= @Size,
		Color		= @Color
	WHERE
		FontID	= @FontID

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

END	

IF @Operation = 2
BEGIN
	DELETE Fonts
	WHERE
		FontID = @FontID

	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].[FontsGet]    Script Date: 04/13/2012 23:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FontsGet]
	@FontID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		FontID,
		FontType,
		[Name],
		Style,
		Size,
		Color
FROM Fonts
WHERE
	@FontID = -1 OR (@FontID <> -1 AND FontID = @FontID)

ORDER BY [Name]
GO

/*****************************************************************************************************/
/******         List of procedures which were removed on version 1.0.0.7 vs version 1.0.0.6		******/
/****** 				TransferredFromChurchGet												******/
/*****************************************************************************************************/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'TransferredFromChurchGet') 
DROP PROCEDURE dbo.TransferredFromChurchGet
GO

/*****************************************************************************************************/
/******         List of procedures which differ between version 1.0.0.7 and version 1.0.0.6		******/
/****** 				ChurchPersonsAllGet														******/
/****** 				ChurchRegistersPersonsGet												******/
/****** 				ChurchesSet																******/
/****** 				FamiliesByChurchGet														******/
/****** 				ChurchRegistersFamiliesGet												******/
/****** 				FamiliesGet																******/
/****** 				MarriageByPersonGet														******/
/****** 				MarriageGet																******/
/****** 				MarriagesSet															******/
/****** 				MembersByChurchGet														******/
/****** 				TransferredPersonsSet													******/
/****** 				TransfersFromGet														******/
/****** 				TransfersGet															******/
/****** 				TransfersSet															******/
/****** 				TransfersToGet															******/
/*****************************************************************************************************/


/****** Object:  StoredProcedure [dbo].[ChurchPersonsAllGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchPersonsAllGet]
	@ChurchID	int = -1
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,
		PersonID,
		P.AddressID,
		A.Address Address, D.DistrictID DistrictID, D.Name District,
		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 C1.Name END) AS ChurchName,
		P.BelongDate
FROM Persons P
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
	LEFT OUTER JOIN Addresses A ON (P.AddressID = A.AddressID)
    LEFT OUTER JOIN Districts D ON (A.DistrictID = D.DistrictID)
WHERE (P.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[TransferredPersonsSet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransferredPersonsSet]
	@PersonID				int = -1,
	@BelongDate				datetime,
	@ChurchID				int,
	@ChurchName				nvarchar(50)
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0

-- check parameters

IF NOT EXISTS(SELECT * FROM Persons WHERE PersonID = @PersonID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END


UPDATE Persons
SET
	ChurchID = @ChurchID,
	ChurchName = @ChurchName,
	BelongDate = @BelongDate
WHERE
	PersonID = @PersonID


IF @@ERROR <> 0
BEGIN
	SET @ErrorCode = 4
	GOTO __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].[TransfersToGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersToGet]
	@ChurchID 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,
	LastName,
	FirstName,
	MaidenName,
	T.TransferID, 
	T.TransferFromChurchID, 
	(case when TransferFromChurchID IS NULL THEN TransferFromChurchName else CF.Name END) AS TransferFromChurchName,
	T.TransferToChurchID, 
	(case when TransferToChurchID IS NULL THEN TransferToChurchName else CT.Name END) AS TransferToChurchName,
	T.TransferFromDate,
	T.TransferToDate,
	T.TransferFromAct,
	T.TransferToAct,
	T.TransferDetails, 
	AddressID,
	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 C1.Name END) AS ChurchName,
	P.BelongDate
FROM Persons P
	INNER JOIN Transfers T ON P.PersonID = T.PersonID
		LEFT OUTER JOIN Churches CF ON (T.TransferFromChurchID = CF.ChurchID)
		LEFT OUTER JOIN Churches CT ON (T.TransferToChurchID = CT.ChurchID)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE ((T.TransferToChurchID = @ChurchID) OR @ChurchID = -1 OR (T.TransferToChurchID IS NULL AND @ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[TransfersSet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersSet]
	@Operation					int = -1,
	@TransferID					int = -1 OUTPUT,
	@PersonID					int,	
	@TransferFromChurchID		int,	
	@TransferFromChurchName		nvarchar(50),	
	@TransferToChurchID			int,	
	@TransferToChurchName		nvarchar(50),	
	@TransferFromDate			datetime,
	@TransferToDate				datetime,
	@TransferFromAct			nvarchar(50),
	@TransferToAct				nvarchar(50),
	@TransferDetails			nvarchar(256)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Transfers (	PersonID, 
						TransferFromChurchID, 
						TransferFromChurchName, 
						TransferToChurchID,
						TransferToChurchName,
						TransferFromDate,
						TransferToDate,
						TransferFromAct,
						TransferToAct,
						TransferDetails)
	VALUES( @PersonID, 
			@TransferFromChurchID, 
			@TransferFromChurchName, 
			@TransferToChurchID,
			@TransferToChurchName,
			@TransferFromDate,
			@TransferToDate,
			@TransferFromAct,
			@TransferToAct,
			@TransferDetails)

	SET @TransferID = @@IDENTITY

	UPDATE Members
	SET
		IsActive = 0,
		RetireDate = @TransferFromDate
	WHERE 
		IsActive = 1 AND @PersonID = PersonID


	IF @TransferToChurchID IS NOT NULL
	BEGIN
		INSERT Members(ChurchID, Church, PersonID, IsActive, BecomeDate, RetireDate, Details)
		VALUES(@TransferToChurchID, @TransferToChurchName, @PersonID, 1, @TransferToDate, NULL, NULL)				
	END
	

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

IF @Operation = 1
BEGIN
	UPDATE Transfers
	SET
		--PersonID = @PersonID,
		TransferFromChurchID = @TransferFromChurchID,
		TransferFromChurchName = @TransferFromChurchName,
		TransferToChurchID = @TransferToChurchID,
		TransferToChurchName = @TransferToChurchName,
		TransferFromDate = @TransferFromDate,
		TransferToDate = @TransferToDate,
		TransferFromAct = @TransferFromAct,
		TransferToAct = @TransferToAct,
		TransferDetails = @TransferDetails
	WHERE
		TransferID = @TransferID

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

END	

IF @Operation = 2
BEGIN
	DELETE Transfers
	WHERE
		TransferID = @TransferID

	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].[TransfersGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersGet]
	@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,
	P.PersonID,
	AddressID,
	LastName,
	FirstName,
	MaidenName,
	T.TransferID TransferID, 
	T.TransferFromChurchID,
	(case when TransferFromChurchID IS NULL THEN TransferFromChurchName else CF.Name END) AS TransferFromChurchName,
	T.TransferToChurchID, 
	(case when TransferToChurchID IS NULL THEN TransferToChurchName else CT.Name END) AS TransferToChurchName,
	T.TransferFromDate,
	T.TransferToDate,
	T.TransferFromAct,
	T.TransferToAct,
	T.TransferDetails, 
	AddressID,
	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 Transfers T
	LEFT OUTER JOIN Churches CF ON (T.TransferFromChurchID = CF.ChurchID)
	LEFT OUTER JOIN Churches CT ON (T.TransferToChurchID = CT.ChurchID)
	INNER JOIN Persons P ON P.PersonID = T.PersonID
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
	WHERE (@PersonID = -1 OR P.PersonID = @PersonID)
ORDER BY T.TransferFromDate, P.LastName, P.FirstName
GO
/****** Object:  StoredProcedure [dbo].[TransfersFromGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersFromGet]
	@ChurchID 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,
	LastName,
	FirstName,
	MaidenName,
	T.TransferID TransferID, 
	T.TransferFromChurchID,
	(case when TransferFromChurchID IS NULL THEN TransferFromChurchName else CF.Name END) AS TransferFromChurchName,
	T.TransferToChurchID, 
	(case when TransferToChurchID IS NULL THEN TransferToChurchName else CT.Name END) AS TransferToChurchName,
	T.TransferFromDate,
	T.TransferToDate,
	T.TransferFromAct,
	T.TransferToAct,
	T.TransferDetails, 
	AddressID,
	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 C1.Name END) AS ChurchName,
	P.BelongDate	
FROM Persons P
	INNER JOIN Transfers T ON P.PersonID = T.PersonID
		LEFT OUTER JOIN Churches CF ON (T.TransferFromChurchID = CF.ChurchID)
		LEFT OUTER JOIN Churches CT ON (T.TransferToChurchID = CT.ChurchID)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE ((T.TransferFromChurchID = @ChurchID) OR @ChurchID = -1 OR (T.TransferFromChurchID IS NULL AND @ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[MarriagesSet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MarriagesSet]
	@Operation						int = -1,
	@MarriageID						int = -1 OUTPUT,
	@Divorce						bit,
	@HusbandID						int,
	@WifeID							int,
	@CivilServiceDate		datetime,
	@ReligiousServiceDate	datetime,
	@WeddingChurchID				int,
	@WeddingChurch					nvarchar(50),
	@WeddingServants				nvarchar(256),
	@DivorceDate					datetime,
	@MarriageDetails				nvarchar(256)
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Marriages (Divorce, HusbandID, WifeID, CivilServiceDate, ReligiousServiceDate, WeddingChurchID, WeddingChurch, WeddingServants, DivorceDate, Details)
	VALUES(@Divorce, @HusbandID, @WifeID, @CivilServiceDate, @ReligiousServiceDate, @WeddingChurchID, @WeddingChurch, @WeddingServants, @DivorceDate, @MarriageDetails)

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

IF @Operation = 1
BEGIN
	UPDATE Marriages
	SET
		Divorce = @Divorce,
		HusbandID = @HusbandID,
		WifeID = @WifeID,
		CivilServiceDate = @CivilServiceDate,
		ReligiousServiceDate = @ReligiousServiceDate,
		WeddingChurchID = @WeddingChurchID,
		WeddingChurch = @WeddingChurch,
		WeddingServants = @WeddingServants,
		DivorceDate = @DivorceDate,
		Details = @MarriageDetails
	WHERE
		MarriageID = @MarriageID

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

END	

IF @Operation = 2
BEGIN
	DELETE Marriages
	WHERE
		MarriageID = @MarriageID

	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].[MarriageGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MarriageGet]
	@MarriageID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT 
		Divorce,
		MarriageID,
		HusbandID,
		WifeID,
		CivilServiceDate,
		ReligiousServiceDate,
		WeddingChurchID,
		(case when WeddingChurchID IS NULL THEN WeddingChurch else CM.Name END) AS WeddingChurch,
		WeddingServants,
		DivorceDate,
		Details MarriageDetails
FROM Marriages M	
	LEFT OUTER JOIN Churches CM ON (M.WeddingChurchID = CM.ChurchID)
WHERE (M.MarriageID = @MarriageID)
GO
/****** Object:  StoredProcedure [dbo].[MarriageByPersonGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MarriageByPersonGet]
	@PersonID	int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT 
		Divorce,

		(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,
		-- 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,
		
		MarriageID,
		HusbandID,
		WifeID,
		CivilServiceDate,
		ReligiousServiceDate,
		WeddingChurchID,
		(case when WeddingChurchID IS NULL THEN WeddingChurch else CM.Name END) AS WeddingChurch,
		WeddingServants,
		DivorceDate,
		M.Details MarriageDetails

FROM Marriages M	
	LEFT OUTER JOIN Churches CM ON (M.WeddingChurchID = CM.ChurchID)
	LEFT OUTER JOIN Persons P 
		ON (((M.WifeID = P.PersonID) OR (M.HusbandID = P.PersonID)) AND P.PersonID <> @PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
WHERE (M.HusbandID = @PersonID OR M.WifeID = @PersonID)
ORDER BY M.CivilServiceDate ASC, DivorceDate ASC
GO
/****** Object:  StoredProcedure [dbo].[ChurchesSet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesSet]
	@Operation		int = -1,
	@ChurchID		int = -1 OUTPUT,
	@Name			nvarchar(50),
	@Address		nvarchar(256),
	@IsChild		bit
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0 AND @IsChild = 0 
	AND EXISTS(SELECT * FROM Churches WHERE IsChild = @IsChild)
BEGIN
	SET @ErrorCode = 11
	GOTO __END
END

IF @Operation = 1 AND @IsChild = 0 
	AND EXISTS(SELECT * FROM Churches WHERE IsChild = @IsChild AND ChurchID <> @ChurchID)
BEGIN
	SET @ErrorCode = 11
	GOTO __END
END

IF @Operation = 0
BEGIN 
	INSERT Churches ([Name], Address, IsChild)
	VALUES(@Name, @Address, @IsChild)

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

IF @Operation = 1
BEGIN
	UPDATE Churches
	SET
		[Name] = @Name,
		[Address] = @Address,
		IsChild = @IsChild
	WHERE
		ChurchID = @ChurchID

	UPDATE Baptisms
		SET BaptismChurch = @Name
	WHERE BaptismChurchID=@ChurchID

	UPDATE Blessings
		SET BlessingChurch = @Name
	WHERE BlessingChurchID=@ChurchID

	UPDATE Marriages
		SET WeddingChurch = @Name
	WHERE WeddingChurchID=@ChurchID

	UPDATE Members
		SET Church = @Name
	WHERE ChurchID=@ChurchID

	UPDATE Persons
		SET ChurchName = @Name
	WHERE ChurchID=@ChurchID

	UPDATE Transfers
		SET TransferFromChurchName = @Name
	WHERE TransferFromChurchID = @ChurchID

	UPDATE Transfers
		SET TransferToChurchName = @Name
	WHERE TransferToChurchID = @ChurchID


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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT CS.ChurchID FROM ChurchesServants CS
		WHERE(CS.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 12
		GOTO __END
	END

	IF EXISTS (SELECT T.TransferFromChurchID, T.TransferToChurchID FROM Transfers T
		WHERE ((T.TransferFromChurchID = @ChurchID) OR (T.TransferToChurchID = @ChurchID)))
	BEGIN
		SET @ErrorCode = 13
		GOTO __END
	END

	IF EXISTS (SELECT B.BaptismChurchID FROM Baptisms B
		WHERE(B.BaptismChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 14
		GOTO __END
	END
	
	IF EXISTS (SELECT B.BlessingChurchID FROM Blessings B
		WHERE(B.BlessingChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 15
		GOTO __END
	END

	IF EXISTS (SELECT P.ChurchID FROM Persons P
		WHERE(P.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 16
		GOTO __END
	END

	IF EXISTS (SELECT CP.ChurchID FROM ChurchesProperties CP
		WHERE(CP.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 17
		GOTO __END
	END
	
	IF EXISTS (SELECT M.WeddingChurchID FROM Marriages M
		WHERE(M.WeddingChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 18
		GOTO __END
	END

	IF EXISTS (SELECT CGP.ChurchID FROM ChurchesGroupsPersons CGP
		WHERE(CGP.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 19
		GOTO __END
	END

	IF EXISTS (SELECT D.ChurchID FROM Departures D
		WHERE(D.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 20
		GOTO __END
	END

	IF EXISTS (SELECT SC.ChurchID FROM SpecialCases SC
		WHERE(SC.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 21
		GOTO __END
	END
	
	IF EXISTS (SELECT M.ChurchID FROM Members M
		WHERE(M.ChurchID = @ChurchID))
	BEGIN
		SET @ErrorCode = 22
		GOTO __END
	END

	DELETE Churches
	WHERE
		ChurchID = @ChurchID

	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].[MembersByChurchGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MembersByChurchGet]
	@ChurchID	int = -1,
	@IsActive	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,
		M.IsActive,
		--dbo.IsBaptized(P.PersonID) IsBaptized,
		M.MemberID,
		M.ChurchID MemberChurchID,
		(case when M.ChurchID IS NULL THEN M.Church else C1.Name END) AS MemberChurch,
		M.BecomeDate,
		M.RetireDate,
		M.Details MemberDetails, 
		M.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,  
		P.Details,
		--P.ChurchID,
		--(case when P.ChurchID IS NULL THEN P.ChurchName else C1.Name END) AS ChurchName
		--P.BelongDate
		B2.BaptismDate,
		(case when B2.BaptismChurchID IS NULL THEN B2.BaptismChurch else C2.Name END) AS BaptismChurch,
		B2.BaptismServant

FROM Persons P
	INNER JOIN Members M ON M.PersonID = P.PersonID
		LEFT OUTER JOIN Churches C1 ON (M.ChurchID = C1.ChurchID)
	LEFT OUTER JOIN Baptisms B2 ON P.PersonID = B2.PersonID
		LEFT OUTER JOIN Churches C2 ON (B2.BaptismChurchID = C2.ChurchID)

WHERE (M.ChurchID = @ChurchID OR @ChurchID = -1) AND (M.IsActive = @IsActive OR @IsActive = -1)
ORDER BY M.BecomeDate
GO

/****** Object:  StoredProcedure [dbo].[FamiliesByChurchGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamiliesByChurchGet]
	@ChurchID	int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

/* HusbandLastMarriages */
DECLARE @HusbandLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @HusbandLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select HusbandID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by HusbandID) MH ON M.HusbandID = MH.HusbandID
	inner join Persons P ON M.HusbandID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MH.MarriageServiceDate)
	AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

/* WifeLastMarriages */
DECLARE @WifeLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @WifeLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select WifeID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by WifeID) MW ON M.WifeID = MW.WifeID
	inner join Persons P ON M.WifeID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MW.MarriageServiceDate)
	AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

	
	SELECT M.MarriageID,
		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,
		ISNULL(MH.IsActive, 0) AS IsHusbandMember,
		ISNULL(MW.IsActive, 0) AS IsWifeMember,
		(case when ISNULL(MH.IsActive, 0) = 1 OR ISNULL(MW.IsActive, 0) = 1 then cast(1 as bit) else cast(0 as bit) END) IsHusbandOrWifeMember,
		(case when ISNULL(MH.IsActive, 0) = 1 AND ISNULL(MW.IsActive, 0) = 1 then cast(1 as bit) else cast(0 as bit) END) IsHusbandAndWifeMember,
		(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,
		(case when HLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandLastMarriage, 
		(case when WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS WifeLastMarriage,
		(case when HLM.MarriageID IS NOT NULL OR WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandOrWifeLastMarriage,
		PH.DeathDate HusbandDeathDate, 
		PW.DeathDate WifeDeathDate,
		M.DivorceDate,
		M.Details MarriageDetails
	FROM Marriages M
		LEFT OUTER JOIN Churches C ON (M.WeddingChurchID = C.ChurchID)
		LEFT OUTER JOIN Persons PH ON (M.HusbandID = PH.PersonID)
		LEFT OUTER JOIN Persons PW ON (M.WifeID = PW.PersonID)
		LEFT OUTER JOIN Members MH ON (M.HusbandID = MH.PersonID AND MH.IsActive = 1) 
		LEFT OUTER JOIN Members MW ON (M.WifeID = MW.PersonID AND MW.IsActive = 1) 
		LEFT OUTER JOIN @HusbandLastMarriages HLM ON (HLM.MarriageID = M.MarriageID) 
		LEFT OUTER JOIN @WifeLastMarriages WLM ON (WLM.MarriageID = M.MarriageID) 
	WHERE (PW.ChurchID = @ChurchID OR (PW.ChurchID IS NULL AND @ChurchID IS NULL)) 
		OR (PH.ChurchID = @ChurchID OR (PH.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1
	ORDER BY FamilyName
GO



/****** Object:  StoredProcedure [dbo].[ChurchRegistersFamiliesGet]    Script Date: 05/26/2012 20:37:29 ******/
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()


/* HusbandLastMarriages */
DECLARE @HusbandLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @HusbandLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select HusbandID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by HusbandID) MH ON M.HusbandID = MH.HusbandID
	inner join Persons P ON M.HusbandID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MH.MarriageServiceDate)
	AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

/* WifeLastMarriages */
DECLARE @WifeLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @WifeLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select WifeID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by WifeID) MW ON M.WifeID = MW.WifeID
	inner join Persons P ON M.WifeID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MW.MarriageServiceDate)
	AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

	
	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,
		(case when HLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandLastMarriage, 
		(case when WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS WifeLastMarriage,
		(case when HLM.MarriageID IS NOT NULL OR WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandOrWifeLastMarriage,
		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)
		LEFT OUTER JOIN @HusbandLastMarriages HLM ON (HLM.MarriageID = M.MarriageID) 
		LEFT OUTER JOIN @WifeLastMarriages WLM ON (WLM.MarriageID = M.MarriageID) 
		--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].[FamiliesGet]    Script Date: 05/26/2012 21:04:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamiliesGet]
AS
	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()
	
	
/* HusbandLastMarriages */
DECLARE @HusbandLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @HusbandLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select HusbandID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by HusbandID) MH ON M.HusbandID = MH.HusbandID
	inner join Persons P ON M.HusbandID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MH.MarriageServiceDate)
	--AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

/* WifeLastMarriages */
DECLARE @WifeLastMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

Insert INTO @WifeLastMarriages
select M.MarriageID 
from Marriages M
	inner join 
		(select WifeID, max(isnull(CivilServiceDate, isnull(ReligiousServiceDate, DivorceDate))) MarriageServiceDate from Marriages
		group by WifeID) MW ON M.WifeID = MW.WifeID
	inner join Persons P ON M.WifeID = P.PersonID
where ((M.CivilServiceDate IS NULL AND M.ReligiousServiceDate IS NULL AND M.DivorceDate IS NULL) OR
	isnull(M.CivilServiceDate, isnull(M.ReligiousServiceDate, M.DivorceDate)) = MW.MarriageServiceDate)
	--AND ((P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) OR @ChurchID = -1)

	
	SELECT M.MarriageID,
		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,
		(case when HLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandLastMarriage, 
		(case when WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS WifeLastMarriage,
		(case when HLM.MarriageID IS NOT NULL OR WLM.MarriageID IS NOT NULL THEN cast(1 as bit) ELSE cast(0 as bit) END) AS HusbandOrWifeLastMarriage,
		PH.DeathDate HusbandDeathDate, 
		PW.DeathDate WifeDeathDate

	FROM Marriages M
		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)
		LEFT OUTER JOIN @HusbandLastMarriages HLM ON (HLM.MarriageID = M.MarriageID) 
		LEFT OUTER JOIN @WifeLastMarriages WLM ON (WLM.MarriageID = M.MarriageID) 
	ORDER BY FamilyName
GO

/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsGet]    Script Date: 03/31/2012 17:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersPersonsGet]
	@ChurchID		int = -1,
	@RegisterTypeID int = -1,
	@PersonID		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,
    RT.Name RegisterName,
    
	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 RegistersTypes RT ON (CRP.RegisterTypeID = RT.RegisterTypeID)
	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 OR @ChurchID = -1) 
	AND (CRP.RegisterTypeID = @RegisterTypeID OR @RegisterTypeID = -1)
	AND (@PersonID = -1 OR CRP.PersonID = @PersonID)
ORDER BY CRP.RegisterNo
GO


/*****************************************************************************************************/
/******         Update Mariages data from version 1.0.0.6 to version 1.0.0.7 format				******/
/*****************************************************************************************************/

DECLARE @TemporaryMarriages TABLE
(
	[MarriageID] [int] NOT NULL
)

UPDATE Marriages
	SET 
		DivorceDate = CivilServiceDate
WHERE Divorce = 1

UPDATE Marriages
	SET 
		CivilServiceDate = NULL
WHERE Divorce = 1

Insert INTO @TemporaryMarriages
SELECT M2.MarriageID 
FROM Marriages M1
	INNER JOIN Marriages M2	ON M1.MarriageID <> M2.MarriageID AND M1.HusbandID = M2.HusbandID AND M1.WifeID = M2.WifeID	
WHERE M1.Divorce = 0 AND M2.Divorce = 1

UPDATE M1
	SET 
		Divorce = 1,
		DivorceDate = M2.DivorceDate
FROM Marriages M1
	INNER JOIN Marriages M2	ON M1.MarriageID <> M2.MarriageID AND M1.HusbandID = M2.HusbandID AND M1.WifeID = M2.WifeID	
WHERE M1.Divorce = 0 AND M2.Divorce = 1


DELETE FROM Marriages 
WHERE MarriageID IN (SELECT TM.MarriageID FROM @TemporaryMarriages TM) 

GO

/*****************************************************************************************************/
/******         Update LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
/*****************************************************************************************************/

/******         Update Persons LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Persons
	SET MaidenName = NULL
WHERE LEN(LTRIM(RTRIM(MaidenName))) = 0
GO

UPDATE Persons
	SET PlaceOfBirth = NULL
WHERE LEN(LTRIM(RTRIM(PlaceOfBirth))) = 0
GO

UPDATE Persons
	SET CNP = NULL
WHERE LEN(LTRIM(RTRIM(CNP))) = 0
GO

UPDATE Persons
	SET Series = NULL
WHERE LEN(LTRIM(RTRIM(Series))) = 0
GO

UPDATE Persons
	SET HomePhone = NULL
WHERE LEN(LTRIM(RTRIM(HomePhone))) = 0
GO

UPDATE Persons
	SET Details = NULL
WHERE LEN(LTRIM(RTRIM(Details))) = 0
GO

UPDATE Persons
	SET ChurchName = NULL
WHERE LEN(LTRIM(RTRIM(ChurchName))) = 0
GO


/******         Update Addresses LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Addresses
	SET [Address] = NULL
WHERE LEN(LTRIM(RTRIM([Address]))) = 0
GO

/******         Update Baptisms LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Baptisms
	SET BaptismChurch = NULL
WHERE LEN(LTRIM(RTRIM(BaptismChurch))) = 0
GO

UPDATE Baptisms
	SET BaptismServant = NULL
WHERE LEN(LTRIM(RTRIM(BaptismServant))) = 0
GO

/******         Update Blessings LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Blessings
	SET BlessingChurch = NULL
WHERE LEN(LTRIM(RTRIM(BlessingChurch))) = 0
GO

UPDATE Blessings
	SET BlessingServant = NULL
WHERE LEN(LTRIM(RTRIM(BlessingServant))) = 0
GO

/******         Update Churches LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Churches
	SET [Address] = NULL
WHERE LEN(LTRIM(RTRIM([Address]))) = 0
GO

/******         Update ChurchesGroupsPersons LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE ChurchesGroupsPersons
	SET GroupPersonDetails = NULL
WHERE LEN(LTRIM(RTRIM(GroupPersonDetails))) = 0
GO

/******         Update ChurchesRegistersFamilies LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE ChurchesRegistersFamilies
	SET RegisterFamilyDetails = NULL
WHERE LEN(LTRIM(RTRIM(RegisterFamilyDetails))) = 0
GO

/******         Update ChurchesRegistersPersons LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE ChurchesRegistersPersons
	SET RegisterPersonDetails = NULL
WHERE LEN(LTRIM(RTRIM(RegisterPersonDetails))) = 0
GO

/******         Update ChurchesServants LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE ChurchesServants
	SET ServantDetails = NULL
WHERE LEN(LTRIM(RTRIM(ServantDetails))) = 0
GO

/******         Update Departures LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Departures
	SET DepartureAddress = NULL
WHERE LEN(LTRIM(RTRIM(DepartureAddress))) = 0
GO

UPDATE Departures
	SET DepartureDetails = NULL
WHERE LEN(LTRIM(RTRIM(DepartureDetails))) = 0
GO

/******         Update HolySpiritBaptisms LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE HolySpiritBaptisms
	SET HolySpiritBaptismAddress = NULL
WHERE LEN(LTRIM(RTRIM(HolySpiritBaptismAddress))) = 0
GO

/******         Update Marriages LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Marriages
	SET WeddingChurch = NULL
WHERE LEN(LTRIM(RTRIM(WeddingChurch))) = 0
GO

UPDATE Marriages
	SET WeddingServants = NULL
WHERE LEN(LTRIM(RTRIM(WeddingServants))) = 0
GO

UPDATE Marriages
	SET Details = NULL
WHERE LEN(LTRIM(RTRIM(Details))) = 0
GO

/******         Update Members LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Members
	SET Details = NULL
WHERE LEN(LTRIM(RTRIM(Details))) = 0
GO

/******         Update SpecialCases LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE SpecialCases
	SET SpecialCaseDetails = NULL
WHERE LEN(LTRIM(RTRIM(SpecialCaseDetails))) = 0
GO

/******         Update Transfers LEN(LTRIM(RTRIM(ColumnName))) = 0 => NULL                				******/
UPDATE Transfers
	SET TransferFromChurchName = NULL
WHERE LEN(LTRIM(RTRIM(TransferFromChurchName))) = 0
GO

UPDATE Transfers
	SET TransferToChurchName = NULL
WHERE LEN(LTRIM(RTRIM(TransferToChurchName))) = 0
GO

UPDATE Transfers
	SET TransferFromAct = NULL
WHERE LEN(LTRIM(RTRIM(TransferFromAct))) = 0
GO

UPDATE Transfers
	SET TransferToAct = NULL
WHERE LEN(LTRIM(RTRIM(TransferToAct))) = 0
GO

UPDATE Transfers
	SET TransferDetails = NULL
WHERE LEN(LTRIM(RTRIM(TransferDetails))) = 0
GO


/******         Update Persons - BelongDate IS NULL                				******/

UPDATE Persons
	SET 
		BelongDate = M.BecomeDate
FROM Persons P
	INNER JOIN Members M ON P.PersonID = M.PersonID
WHERE (P.ChurchID IS NOT NULL) AND (P.BelongDate IS NULL) AND (M.BecomeDate IS NOT NULL) AND M.IsActive = 1
GO	