USE [Church]
GO

/*****************************************************************************************************/
/******         List of tables which exist in version 1.0.0.5 but not in version 1.0.0.4		******/
/****** 				Members																	******/
/*****************************************************************************************************/


/****** Object:  Table [dbo].[Members]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Members](
	[MemberID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NOT NULL,
	[Church] [nvarchar](50) NULL,
	[PersonID] [int] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[BecomeDate] [datetime] NULL,
	[RetireDate] [datetime] NULL,
	[Details] [nvarchar](255) NULL,
 CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED 
(
	[MemberID] 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
CREATE NONCLUSTERED INDEX [IX_Members] ON [dbo].[Members] 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Members_1] ON [dbo].[Members] 
(
	[ChurchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/*****************************************************************************************************/
/******         List of tables which differ between version 1.0.0.5 and version 1.0.0.4			******/
/****** 				ChurchesRegistersFamilies												******/
/****** 				ChurchesRegistersPersons												******/
/****** 				Departures																******/
/****** 				Grids																	******/
/****** 				Persons																	******/
/****** 				SpecialCases															******/
/*****************************************************************************************************/




/****** 	ChurchesRegistersFamilies		******/
--ALTER TABLE ChurchesRegistersFamilies 
DROP INDEX ChurchesRegistersFamilies.IX_ChurchesRegistersFamilies
GO
				 
ALTER TABLE ChurchesRegistersFamilies 
DROP CONSTRAINT [PK_ChurchRegistersFamilies]
GO

ALTER TABLE ChurchesRegistersFamilies 
ADD ChurchRegisterFamilyID INT IDENTITY(1,1) NOT NULL
GO

ALTER TABLE ChurchesRegistersFamilies 
ADD CONSTRAINT PK_ChurchesRegistersFamilies PRIMARY KEY (ChurchRegisterFamilyID)
GO


CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersFamilies] ON [dbo].[ChurchesRegistersFamilies] 
(
	[ChurchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersFamilies_1] ON [dbo].[ChurchesRegistersFamilies] 
(
	[RegisterTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersFamilies_2] ON [dbo].[ChurchesRegistersFamilies] 
(
	[MarriageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersFamilies_3] ON [dbo].[ChurchesRegistersFamilies] 
(
	[RegisterNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


ALTER TABLE ChurchesRegistersFamilies 
ADD IsActive bit NOT NULL DEFAULT(1)
GO

/****** 	ChurchesRegistersPersons		******/
--ALTER TABLE ChurchesRegistersPersons 
DROP INDEX ChurchesRegistersPersons.IX_ChurchesRegistersPersons
GO

ALTER TABLE ChurchesRegistersPersons 
DROP CONSTRAINT [PK_ChurchesRegistersPersons]
GO
	 
ALTER TABLE ChurchesRegistersPersons 
ADD ChurchRegisterPersonID INT IDENTITY(1,1) NOT NULL
GO

ALTER TABLE ChurchesRegistersPersons 
ADD CONSTRAINT PK_ChurchesRegistersPersons PRIMARY KEY (ChurchRegisterPersonID)
GO


CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersPersons] ON [dbo].[ChurchesRegistersPersons] 
(
	[ChurchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersPersons_1] ON [dbo].[ChurchesRegistersPersons] 
(
	[RegisterTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersPersons_2] ON [dbo].[ChurchesRegistersPersons] 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ChurchesRegistersPersons_3] ON [dbo].[ChurchesRegistersPersons] 
(
	[RegisterNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


ALTER TABLE ChurchesRegistersPersons 
ADD IsActive bit NOT NULL DEFAULT(1)
GO

/****** 	Departures						******/
ALTER TABLE Departures
ADD ChurchID int
GO

CREATE NONCLUSTERED INDEX [IX_Departures_1] ON [dbo].[Departures] 
(
	[ChurchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** 	Grids							******/
DELETE FROM GridsColumns
DELETE FROM Grids
GO

ALTER TABLE Grids
ADD MultilineHeader tinyint
GO

/****** 	Persons							******/
ALTER TABLE Persons
ADD	BelongDate datetime
GO

/****** 	SpecialCases					******/
ALTER TABLE SpecialCases

ADD ChurchID int
GO

CREATE NONCLUSTERED INDEX [IX_SpecialCases_1] ON [dbo].[SpecialCases] 
(
	[ChurchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/*****************************************************************************************************/
/******     List of procedures which exist in version 1.0.0.5 but not in version 1.0.0.4		******/
/*****************************************************************************************************/



/****** Object:  StoredProcedure [dbo].[BaptizedByMariageGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
GO



/****** Object:  StoredProcedure [dbo].[BaptizedPersonsNotBelongToChurchGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BaptizedPersonsNotBelongToChurchGet]
	@ChurchID 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,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		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, 
		Details,
		P.ChurchID,
		--ChurchName
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName,
		P.BelongDate
	FROM Persons P
		INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
		LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
	WHERE (P.ChurchID <> @ChurchID) 
			OR (@churchID IS NULL AND P.ChurchID IS NOT NULL)
			OR (@ChurchID IS NOT NULL AND P.ChurchID IS NULL)
GO


/****** Object:  StoredProcedure [dbo].[BlessedByMariageGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
GO


/****** Object:  StoredProcedure [dbo].[ChildrenByMariageGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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.Sex DESC
GO


/****** Object:  StoredProcedure [dbo].[HolySpiritBaptizedByMariageGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
GO


/****** Object:  StoredProcedure [dbo].[MarriageGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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

FROM Marriages M	
	LEFT OUTER JOIN Churches CM ON (M.WeddingChurchID = CM.ChurchID)
WHERE (M.MarriageID = @MarriageID)
GO

/****** Object:  StoredProcedure [dbo].[MembersByChurchGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
FROM Persons P
	INNER JOIN Members M ON M.PersonID = P.PersonID
	LEFT OUTER JOIN Churches C1 ON (M.ChurchID = C1.ChurchID)
WHERE (M.ChurchID = @ChurchID OR @ChurchID = -1) AND (M.IsActive = @IsActive OR @IsActive = -1)
GO


/****** Object:  StoredProcedure [dbo].[MembersByMariageGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
GO


/****** Object:  StoredProcedure [dbo].[MembersByPersonGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MembersByPersonGet]
	@PersonID	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,
		--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.IsActive,
		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
FROM Persons P
	INNER JOIN Members M ON M.PersonID = P.PersonID
	LEFT OUTER JOIN Churches C1 ON (M.ChurchID = C1.ChurchID)
WHERE P.PersonID = @PersonID AND (M.IsActive = @IsActive OR @IsActive = -1)
GO


/****** Object:  StoredProcedure [dbo].[MembersSet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MembersSet]
	@Operation					int = -1,
	@MemberID					int = -1 OUTPUT,
	@MemberChurchID				int,
	@MemberChurch				nvarchar(50),
	@PersonID					int,
	@IsActive					bit,
	@BecomeDate					datetime,
	@RetireDate					datetime,
	@MemberDetails				nvarchar(255)
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Members (ChurchID, Church, PersonID, IsActive, BecomeDate, RetireDate, Details)
	VALUES(@MemberChurchID, @MemberChurch, @PersonID, @IsActive, @BecomeDate, @RetireDate, @MemberDetails)

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

END

IF @Operation = 1
BEGIN
	UPDATE Members
	SET
		ChurchID = @MemberChurchID,
		Church = @MemberChurch,
		PersonID = @PersonID,
		IsActive = @IsActive,
		BecomeDate = @BecomeDate,
		RetireDate = @RetireDate,
		Details = @MemberDetails

	WHERE MemberID = @MemberID

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

END	

IF @Operation = 2
BEGIN
	DELETE Members
	WHERE MemberID = @MemberID

	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].[ParentsGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ParentsGet]
	@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.Photo,
		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 (M.WifeID = P.PersonID OR M.HusbandID = P.PersonID)
			LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)

	WHERE
		MarriageID = @MarriageID
	ORDER BY P.Sex DESC
GO



/*****************************************************************************************************/
/******         List of procedures which differ between version 1.0.0.5 and version 1.0.0.4		******/
/****** 				Members																	******/
/*****************************************************************************************************/

/****** Object:  StoredProcedure [dbo].[BaptismByPersonGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptismByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT BaptismID, PersonID, BaptismChurchID, BaptismDate, BaptismChurch, BaptismServant 
FROM Baptisms
WHERE PersonID = @PersonID
GO


/****** Object:  StoredProcedure [dbo].[BaptismPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptismPersonsGet]
	@BaptismChurchID int = -1,
	@BaptismDate datetime
AS

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

	/* SET NOCOUNT ON */

    SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		ISNULL(M.IsActive, 0) AS IsMember,
		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 C.Name END) AS ChurchName,
		P.BelongDate
	FROM Persons P
		INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
		LEFT OUTER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1) 
	WHERE (B.BaptismChurchID = @BaptismChurchID OR (@BaptismChurchID IS NULL AND B.BaptismChurchID IS NULL) )
		AND (B.BaptismDate = @BaptismDate OR (B.BaptismDate IS NULL AND @BaptismDate IS NULL))
GO


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

	/* SET NOCOUNT ON */

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
SELECT
	B.BaptismID,
	PersonID,
	BaptismChurchID,
	--BaptismChurch,
	(case when BaptismChurchID IS NOT NULL THEN @churchName else BaptismChurch END) AS BaptismChurch,
	BaptismServant,
	B.BaptismDate
FROM Baptisms B 
	INNER JOIN (SELECT BaptismDate, MIN(BaptismID) AS BaptismID FROM Baptisms GROUP BY BaptismDate) BD 
		ON B.BaptismID = BD.BaptismID
WHERE (BaptismChurchID = @ChurchID OR (@ChurchID IS NULL AND BaptismChurchID IS NULL))
GO


/****** Object:  StoredProcedure [dbo].[BaptizedPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptizedPersonsGet]
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,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		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, 
		Details,
		P.ChurchID,
		--ChurchName
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName,
		P.BelongDate
	FROM Persons P
		INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
		LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
GO


/****** Object:  StoredProcedure [dbo].[BlessedPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BlessedPersonsGet]
	@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, 
		ISNULL(M.IsActive, 0) AS IsMember,
		B.BlessingID, 
		B.BlessingChurchID, 
		B.BlessingDate, 
		(case when B.BlessingChurchID IS NOT NULL THEN C.Name else BlessingChurch END) AS BlessingChurch, 
		B.BlessingServant,  
		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 Blessings B ON (P.PersonID = B.PersonID)
			LEFT OUTER JOIN Churches C ON (B.BlessingChurchID = C.ChurchID)
		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 @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL)
GO


/****** Object:  StoredProcedure [dbo].[BlessingByPersonGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BlessingByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT BlessingID, PersonID, BlessingChurchID, BlessingDate, BlessingChurch, BlessingServant 
FROM Blessings
WHERE PersonID = @PersonID
GO


/****** Object:  StoredProcedure [dbo].[ChildrenByPersonGet]    Script Date: 02/19/2011 17:16:42 ******/
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)
GO


/****** Object:  StoredProcedure [dbo].[ChildrenSet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChildrenSet]
	@Operation				int = -1,
	@PersonID				int = -1 OUTPUT,
	@AddressID				int,
	@LastName				nvarchar(50),
	@FirstName				nvarchar(50),
	@MaidenName				nvarchar(50),
	@FatherID				int,
	@MotherID				int,
	@BirthDate				datetime,
	@PlaceOfBirth			nvarchar(50),
	@CNP					nvarchar(50),
	@Series					nvarchar(50),
	@SeriesNumber			nvarchar(50),
	@Sex					bit,
	@Photo					varbinary(MAX),
	@HomePhone				nvarchar(50),
	@MobilePhone			nvarchar(50),
	@DeathDate				datetime,
	@Details				nvarchar(256),
	@ChurchID				int,
	@ChurchName				nvarchar(50),
	@BelongDate				datetime
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


-- check parameters

/*
IF @Operation IN (0, 1) AND EXISTS(SELECT * FROM Persons 
	WHERE CNP = @CNP AND PersonID <> @PersonID)
BEGIN
	SET @ErrorCode = 1
	GOTO __END
END
*/

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

IF @Operation = 0
BEGIN 
	INSERT Persons (	AddressID,
						LastName,
						FirstName,
						MaidenName,
						FatherID,
						MotherID,
						BirthDate,
						PlaceOfBirth,
						CNP,
						Series,
						SeriesNumber,
						Sex,
						Photo,
						HomePhone,
						MobilePhone,
						DeathDate,
						Details,
						ChurchID,
						ChurchName,
						BelongDate)
	VALUES(	@AddressID,
			@LastName,
			@FirstName,
			@MaidenName,
			@FatherID,
			@MotherID,
			@BirthDate,
			@PlaceOfBirth,
			@CNP,
			@Series,
			@SeriesNumber,
			@Sex,
			@Photo,
			@HomePhone,
			@MobilePhone,
			@DeathDate,
			@Details,
			@ChurchID,
			@ChurchName,
			@BelongDate)

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

IF @Operation = 1
BEGIN
	UPDATE Persons
	SET
		FatherID = @FatherID,
		MotherID = @MotherID

	WHERE
		PersonID = @PersonID

	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 4
		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].[ChurchBaptizedPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchBaptizedPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

    SELECT 
		B.BaptismID, 
		B.BaptismChurchID, 
		(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,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		B.BaptismDate, 
		(CASE WHEN B.BaptismDate IS NOT NULL THEN 
			(CASE WHEN DeathDate IS NULL THEN dbo.fn_CalculateAge(B.BaptismDate, @currentDate) 
				ELSE dbo.fn_CalculateAge(B.BaptismDate, DeathDate) END) END) BaptizedAges,
		(case when B.BaptismChurchID IS NULL THEN BaptismChurch else C.Name END) AS BaptismChurch, 
		B.BaptismServant,  
		P.PersonID,
		(case when (SELECT MIN(T.TransferID) from Transfers T where P.PersonID = T.PersonID) 
			IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS WasTransferred,
		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 Baptisms B ON (P.PersonID = B.PersonID)
			LEFT OUTER JOIN Churches C ON (B.BaptismChurchID = C.ChurchID)
		LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.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)
    WHERE P.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL)
GO


/****** Object:  StoredProcedure [dbo].[ChurchChildrenGet]    Script Date: 02/19/2011 17:16:43 ******/
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 IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID)
	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].[ChurchesByTypeGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesByTypeGet]
	@IsChild	bit = false
AS

	/* SET NOCOUNT ON */

SELECT
	ChurchID,
	[Name],
	[Address],
	IsChild

FROM Churches
WHERE
	IsChild = @IsChild
GO


/****** Object:  StoredProcedure [dbo].[ChurchesServantsSet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesServantsSet]
	@Operation			int = -1,
	@ChurchServantID	int = -1 OUTPUT,
	@ServantChurchID	int,
	@ServantID			int,
	@ServantTitleID		int,
	@IsActive			bit,
	@ServantBecomeDate	datetime,
	@ServantRetireDate	datetime,
	@ServantDetails		nvarchar(1024)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT ChurchesServants (ChurchID, ServantID, ServantTitleID, IsActive, ServantBecomeDate, ServantRetireDate, ServantDetails)
	VALUES(@ServantChurchID, @ServantID, @ServantTitleID, @IsActive, @ServantBecomeDate, @ServantRetireDate, @ServantDetails)

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

IF @Operation = 1
BEGIN
	UPDATE ChurchesServants
	SET
		ChurchID = @ServantChurchID,
		ServantID = @ServantID,
		ServantTitleID = @ServantTitleID,
		IsActive = @IsActive,
		ServantBecomeDate = @ServantBecomeDate,
		ServantRetireDate = @ServantRetireDate,
		ServantDetails = @ServantDetails
	WHERE
		ChurchServantID = @ChurchServantID

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

END	

IF @Operation = 2
BEGIN
	DELETE ChurchesServants
	WHERE
		ChurchServantID = @ChurchServantID

	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].[ChurchesSet]    Script Date: 02/19/2011 17:16:42 ******/
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

	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.TransferredFromChurchID, T.TransferredToChurchID FROM Transfers T
		WHERE ((T.TransferredFromChurchID = @ChurchID) OR (T.TransferredToChurchID = @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].[ChurchGroupsPersonsGet]    Script Date: 02/19/2011 17:16:43 ******/
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,
		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 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)
WHERE CGP.ChurchID = @ChurchID AND CGP.GroupTypeID = @GroupTypeID
GO


/****** Object:  StoredProcedure [dbo].[ChurchMembersGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchMembersGet]
	@ChurchID	int = -1
AS

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID

DECLARE @currentDate datetime
SET @currentDate = GETDATE()

SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		ISNULL(M.IsActive, 0) AS IsMember,
		-- dbo.IsBaptized(P.PersonID) IsMember,
		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,
		-- Photo,
		HomePhone,
		MobilePhone,
		DeathDate,	
		P.Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName,
		P.BelongDate
FROM Persons P
	INNER JOIN Members M ON (P.PersonID = M.PersonID AND M.IsActive = 1) 
WHERE ((M.ChurchID IS NULL AND @ChurchID IS NULL) OR M.ChurchID = @ChurchID)
GO


/****** Object:  StoredProcedure [dbo].[ChurchPersonsAllGet]    Script Date: 02/19/2011 17:16:42 ******/
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,
		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 C1.Name END) AS ChurchName,
		P.BelongDate
FROM Persons P
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE (P.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO


/****** Object:  StoredProcedure [dbo].[ChurchPersonsBySexGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

ALTER PROCEDURE [dbo].[ChurchPersonsBySexGet]
	@ChurchID	int = -1,
	@Sex		int = 2
AS

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID

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,
		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,
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName,
		P.BelongDate
FROM Persons P
WHERE (P.ChurchID = @ChurchID OR (P.ChurchID IS NULL AND @ChurchID IS NULL)) 
	AND (P.Sex = @Sex OR @Sex = 2)
GO


/****** Object:  StoredProcedure [dbo].[ChurchPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchPersonsGet]
	@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,
		ISNULL(M.IsActive, 0) AS IsMember,
		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,
		-- Photo,
		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
	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 @ChurchID = -1 OR (P.ChurchID IS NULL AND @ChurchID IS NULL))
GO

/****** Object:  StoredProcedure [dbo].[ChurchRegistersFamiliesGet]    Script Date: 02/19/2011 17:16:42 ******/
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.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].[ChurchRegistersFamiliesSet]    Script Date: 02/19/2011 17:16:42 ******/
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,
	@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 (ChurchID, RegisterTypeID, MarriageID, RegisterNo, RegisterFamilyDetails, IsActive)
	VALUES(@ChurchID, @RegisterTypeID, @MarriageID, @RegisterNo, @RegisterFamilyDetails, @IsActive)

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

IF @Operation = 1
BEGIN
	UPDATE ChurchesRegistersFamilies
	SET
		IsActive = @IsActive
	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].[ChurchRegistersPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
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,
	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, @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
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].[ChurchRegistersPersonsSet]    Script Date: 02/19/2011 17:16:42 ******/
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,
	@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 (ChurchID, RegisterTypeID, PersonID, RegisterNo, RegisterPersonDetails, IsActive)
	VALUES(@ChurchID, @RegisterTypeID, @PersonID, @RegisterNo, @RegisterPersonDetails, @IsActive)

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

IF @Operation = 1
BEGIN
	UPDATE ChurchesRegistersPersons
	SET 
		IsActive = @IsActive
	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].[DepartedPersonsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DepartedPersonsGet]
	@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) AS IsAlive,
		ISNULL(M.IsActive, 0) AS IsMember,
		D.DepartureID, 
		D.ChurchID DepartureChurchID,
		D.IsActive,
		D.DepartureDate,
		D.ReturnDate, 
		D.DepartureAddress, 
		D.DepartureDetails,
		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 Departures D ON D.PersonID = P.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 (D.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND D.ChurchID IS NULL))
GO


/****** Object:  StoredProcedure [dbo].[DeparturesSet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeparturesSet]
	@Operation			int = -1,
	@DepartureID		int = -1 OUTPUT,
	@DepartureChurchID	int = NULL,
	@PersonID			int,
	@IsActive			bit,
	@DepartureDate		datetime,
	@ReturnDate			datetime,
	@DepartureAddress	nvarchar(256),
	@DepartureDetails	nvarchar(256)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Departures (ChurchID, PersonID, IsActive, DepartureDate, ReturnDate, DepartureAddress, DepartureDetails)
	VALUES(@DepartureChurchID, @PersonID, @IsActive, @DepartureDate, @ReturnDate, @DepartureAddress, @DepartureDetails)

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

IF @Operation = 1
BEGIN
	UPDATE Departures
	SET
		ChurchID = @DepartureChurchID,
		PersonID = @PersonID,
		IsActive = @IsActive,
		DepartureDate = @DepartureDate,
		DepartureAddress = @DepartureAddress,
		ReturnDate = @ReturnDate,
		DepartureDetails = @DepartureDetails
	WHERE
		DepartureID = @DepartureID

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

END	

IF @Operation = 2
BEGIN
	DELETE Departures
	WHERE
		DepartureID = @DepartureID

	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].[FamiliesByChurchGet]    Script Date: 02/19/2011 17:16:42 ******/
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()
	
	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,
		PH.DeathDate HusbandDeathDate, 
		PW.DeathDate WifeDeathDate
	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) 
	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].[FamiliesGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamiliesGet]
AS
	/* SET NOCOUNT ON */

DECLARE @currentDate datetime
SET @currentDate = GETDATE()
	
	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,
		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)
	ORDER BY FamilyName
GO


/****** Object:  StoredProcedure [dbo].[FamilyChildrenGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamilyChildrenGet]
	@MarriageID	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(M1.IsActive, 0) AS IsMember,
	P.PersonID, 
	P.LastName, 
	P.FirstName,
    P.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,
	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 Persons P 
	INNER JOIN Marriages M ON (M.HusbandID = P.FatherID OR M.WifeID = P.MotherID)
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
	LEFT OUTER JOIN Members M1 ON (P.PersonID = M1.PersonID AND M1.IsActive = 1) 
WHERE M.MarriageID = @MarriageID 
	AND ((M.HusbandID IS NOT NULL AND P.FatherID IS NOT NULL)
	OR (M.WifeID IS NOT NULL AND P.MotherID IS NOT NULL))
ORDER BY P.BirthDate
GO


/****** Object:  StoredProcedure [dbo].[GridsGet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GridsGet]
	@GridID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		GridID,
		AutoSizeColumnsMode,
		MultilineHeader
				
FROM Grids
WHERE
	@GridID = -1 OR (@GridID <> -1 AND GridID = @GridID)

ORDER BY [GridID]
GO


/****** Object:  StoredProcedure [dbo].[GridsSet]    Script Date: 02/19/2011 17:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GridsSet]
	@Operation				int = -1,
	@GridID					int,
	@AutoSizeColumnsMode	int,
	@MultilineHeader		tinyint
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Grids (GridID, AutoSizeColumnsMode, MultilineHeader)
	VALUES(@GridID, @AutoSizeColumnsMode, @MultilineHeader)

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

IF @Operation = 1
BEGIN
	UPDATE Grids
	SET
		AutoSizeColumnsMode = @AutoSizeColumnsMode,
		MultilineHeader = @MultilineHeader
	WHERE
		GridID = @GridID

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

END	

IF @Operation = 2
BEGIN

	DELETE Grids
	WHERE
		GridID = @GridID

	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].[HolySpiritBaptismByPersonGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritBaptismByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT HolySpiritBaptismID, PersonID, HolySpiritBaptismDate, HolySpiritBaptismAddress 
FROM HolySpiritBaptisms
WHERE PersonID = @PersonID
GO


/****** Object:  StoredProcedure [dbo].[HolySpiritBaptizedPersonsGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritBaptizedPersonsGet]
	@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,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		ISNULL(M.IsActive, 0) AS IsMember,
		HSB.HolySpiritBaptismID, HSB.HolySpiritBaptismDate, HSB.HolySpiritBaptismAddress, 
		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 HolySpiritBaptisms HSB ON (P.PersonID = HSB.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 @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO


/****** Object:  StoredProcedure [dbo].[HolySpiritUnBaptizedPersonsGet]    Script Date: 02/19/2011 17:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritUnBaptizedPersonsGet]
	@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,
	dbo.IsBaptized(P.PersonID) IsBaptized,
	ISNULL(M.IsActive, 0) AS IsMember,
	P.PersonID 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
	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 (NOT EXISTS (SELECT * FROM HolySpiritBaptisms B WHERE P.PersonID = B.PersonID))
		AND (P.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO


/****** Object:  StoredProcedure [dbo].[MarriageByPersonGet]    Script Date: 02/19/2011 17:16:42 ******/
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,
		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

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)
GO


/****** Object:  StoredProcedure [dbo].[MaxPersonsRegisterNoGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MaxPersonsRegisterNoGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	

	SELECT  MAX(RegisterNo)
	FROM ChurchesRegistersPersons
	WHERE (ChurchID = @ChurchID OR (ChurchID IS NULL AND @ChurchID IS NULL))
		AND (RegisterTypeID = @RegisterTypeID)
GO
/****** Object:  StoredProcedure [dbo].[MaxFamiliesRegisterNoGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MaxFamiliesRegisterNoGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	

	SELECT  MAX(RegisterNo)
	FROM ChurchesRegistersFamilies
	WHERE (ChurchID = @ChurchID OR (ChurchID IS NULL AND @ChurchID IS NULL))
		AND (RegisterTypeID = @RegisterTypeID)
GO
/****** Object:  StoredProcedure [dbo].[PersonGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonGet]
	@PersonID INT = -1 
AS

	/* SET NOCOUNT ON */

SELECT
		PersonID,
		AddressID,
		LastName,
		FirstName,
		MaidenName,
		FatherID,
		MotherID,
		BirthDate,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		Photo,
		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 PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[TransferredPersonsSet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransferredPersonsSet]
	@Operation				int = -1,
	@PersonID				int = -1,
	@TransferDate			datetime,
	@ChurchID				int,
	@ChurchName				nvarchar(50)
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0

-- check parameters

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


IF @Operation = 1
BEGIN
	UPDATE Persons
	SET
		ChurchID = @ChurchID,
		ChurchName = @ChurchName

	WHERE
		PersonID = @PersonID


	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 4
		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].[UnBaptizedPersonsGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UnBaptizedPersonsGet]
	@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, 
		(case when (SELECT MIN(H.HolySpiritBaptismID) from HolySpiritBaptisms H where P.PersonID = H.PersonID) 
			IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS IsHolySpiritBaptized,
		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 C1.Name END) AS ChurchName,
		P.BelongDate		 
	FROM Persons P
		LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
	WHERE (NOT EXISTS (SELECT * FROM Baptisms B WHERE P.PersonID = B.PersonID)) 
		AND (P.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[TransfersToGet]    Script Date: 02/20/2011 00:59:49 ******/
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 
	T.TransferID, 
	T.TransferredFromChurchID, 
	(case when TransferredFromChurchID IS NULL THEN TransferredFromChurchName else CF.Name END) AS TransferredFromChurchName,
	T.TransferredToChurchID, 
	(case when TransferredToChurchID IS NULL THEN TransferredToChurchName else CT.Name END) AS TransferredToChurchName,
	T.TransferDate,
	T.TransferAct,
	T.TransferDetails, 
	(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,
	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.TransferredFromChurchID = CF.ChurchID)
		LEFT OUTER JOIN Churches CT ON (T.TransferredToChurchID = CT.ChurchID)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE ((T.TransferredToChurchID = @ChurchID) OR @ChurchID = -1 OR (T.TransferredToChurchID IS NULL AND @ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[TransfersSet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersSet]
	@Operation					int = -1,
	@TransferID					int = -1 OUTPUT,
	@PersonID					int,	
	@TransferredFromChurchID	int,	
	@TransferredFromChurchName	nvarchar(50),	
	@TransferredToChurchID		int,	
	@TransferredToChurchName	nvarchar(50),	
	@TransferDate				datetime,
	@TransferAct				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, 
						TransferredFromChurchID, 
						TransferredFromChurchName, 
						TransferredToChurchID,
						TransferredToChurchName,
						TransferDate,
						TransferAct,
						TransferDetails)
	VALUES( @PersonID, 
			@TransferredFromChurchID, 
			@TransferredFromChurchName, 
			@TransferredToChurchID,
			@TransferredToChurchName,
			@TransferDate,
			@TransferAct,
			@TransferDetails)

	SET @TransferID = @@IDENTITY

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


	IF @TransferredToChurchID IS NOT NULL
	BEGIN
		INSERT Members(ChurchID, Church, PersonID, IsActive, BecomeDate, RetireDate, Details)
		VALUES(@TransferredToChurchID, @TransferredToChurchName, @PersonID, 1, @TransferDate, NULL, NULL)				
	END
	

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

IF @Operation = 1
BEGIN
	UPDATE Transfers
	SET
		--PersonID = @PersonID,
		--TransferredFromChurchID = @TransferredFromChurchID,
		--TransferredFromChurchName = @TransferredFromChurchName,
		--TransferredToChurchID = @TransferredToChurchID,
		--TransferredToChurchName = @TransferredToChurchName,
		TransferDate = @TransferDate,
		TransferAct = @TransferAct,
		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: 02/20/2011 00:59:49 ******/
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 
	T.TransferID TransferID, 
	T.TransferredFromChurchID,
	(case when TransferredFromChurchID IS NULL THEN TransferredFromChurchName else CF.Name END) AS TransferredFromChurchName,
	T.TransferredToChurchID, 
	(case when TransferredToChurchID IS NULL THEN TransferredToChurchName else CT.Name END) AS TransferredToChurchName,
	T.TransferDate,
	T.TransferAct,
	T.TransferDetails, 
	(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,
	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.TransferredFromChurchID = CF.ChurchID)
	LEFT OUTER JOIN Churches CT ON (T.TransferredToChurchID = 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.TransferDate, P.LastName, P.FirstName
GO
/****** Object:  StoredProcedure [dbo].[TransfersFromGet]    Script Date: 02/20/2011 00:59:49 ******/
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 T.TransferID TransferID, 
	T.TransferredFromChurchID,
	(case when TransferredFromChurchID IS NULL THEN TransferredFromChurchName else CF.Name END) AS TransferredFromChurchName,
	T.TransferredToChurchID, 
	(case when TransferredToChurchID IS NULL THEN TransferredToChurchName else CT.Name END) AS TransferredToChurchName,
	T.TransferDate,
	T.TransferAct,
	T.TransferDetails, 
	(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,
	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.TransferredFromChurchID = CF.ChurchID)
		LEFT OUTER JOIN Churches CT ON (T.TransferredToChurchID = CT.ChurchID)
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
WHERE ((T.TransferredFromChurchID = @ChurchID) OR @ChurchID = -1 OR (T.TransferredFromChurchID IS NULL AND @ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[PersonsByPhotoGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsByPhotoGet]
	@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,
		ISNULL(M.IsActive, 0) AS IsMember,
		(case when Photo IS NULL THEN cast(0 as bit) else cast(1 as bit) END) HasPhoto,
		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  
	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 @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[PersonsAddressesGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsAddressesGet]
	@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, 
	ISNULL(M.IsActive, 0) AS IsMember,
	A.Address Address, D.DistrictID DistrictID, D.Name District,
	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
FROM Persons P 
	LEFT OUTER JOIN Addresses A ON (P.AddressID = A.AddressID)
    LEFT OUTER JOIN Districts D ON (A.DistrictID = D.DistrictID)
    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 @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[SpecialCasesSet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpecialCasesSet]
	@Operation				int = -1,
	@SpecialCaseID			int = -1 OUTPUT,
	@SpecialCaseChurchID	int = NULL,
	@PersonID				int,
	@IsActive				bit,	
	@SpecialCaseDate		datetime,
	@SolvedDate				datetime,	
	@SpecialCaseDetails		nvarchar(256)	

AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT SpecialCases (ChurchID, PersonID, IsActive, SpecialCaseDate, SolvedDate, SpecialCaseDetails)
	VALUES(@SpecialCaseChurchID, @PersonID, @IsActive, @SpecialCaseDate, @SolvedDate, @SpecialCaseDetails)
	

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

IF @Operation = 1
BEGIN
	UPDATE SpecialCases
	SET
		ChurchID = @SpecialCaseChurchID,
		PersonID = @PersonID,
		IsActive = @IsActive,
		SpecialCaseDate = @SpecialCaseDate,
		SolvedDate = @SolvedDate,
		SpecialCaseDetails = @SpecialCaseDetails
	WHERE
		SpecialCaseID = @SpecialCaseID

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

END	

IF @Operation = 2
BEGIN
	DELETE SpecialCases
	WHERE
		SpecialCaseID = @SpecialCaseID

	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].[SpecialCasesPersonsGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpecialCasesPersonsGet]
	@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, 
		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 (SC.ChurchID = @ChurchID OR @ChurchID = -1 OR (@ChurchID IS NULL AND SC.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[ServantsGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ServantsGet]
	@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,
		(case when H.HolySpiritBaptismID IS NOT NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsHolySpiritBaptized,
		ST.ServantTitleID, 
		ST.Name ServantTitle,
		CS.ChurchServantID, 
		CS.ChurchID ServantChurchID, 
		CS.ServantID, 
		CS.IsActive,
		CS.ServantBecomeDate,
		CS.ServantRetireDate,
		CS.ServantDetails,
		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,
		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 ChurchesServants CS ON CS.ServantID = P.PersonID
	INNER JOIN ServantsTitles ST ON CS.ServantTitleID = ST.ServantTitleID
	LEFT OUTER JOIN Churches C1 ON (P.ChurchID = C1.ChurchID)
	LEFT OUTER JOIN HolySpiritBaptisms H ON (P.PersonID = H.PersonID)
WHERE ((CS.ChurchID = @ChurchID) OR @ChurchID = -1 OR (CS.ChurchID IS NULL AND @ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[PersonsSet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsSet]
	@Operation				int = -1,
	@PersonID				int = -1 OUTPUT,
	@AddressID				int,
	@LastName				nvarchar(50),
	@FirstName				nvarchar(50),
	@MaidenName				nvarchar(50),
	@FatherID				int,
	@MotherID				int,
	@BirthDate				datetime,
	@PlaceOfBirth			nvarchar(50),
	@CNP					nvarchar(50),
	@Series					nvarchar(50),
	@SeriesNumber			nvarchar(50),
	@Sex					bit,
	@Photo					varbinary(MAX),
	@HomePhone				nvarchar(50),
	@MobilePhone			nvarchar(50),
	@DeathDate				datetime,
	@Details				nvarchar(256),
	@ChurchID				int,
	@ChurchName				nvarchar(50),
	@BelongDate				datetime
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


-- check parameters

/*
IF @Operation IN (0, 1) AND EXISTS(SELECT * FROM Persons 
	WHERE CNP = @CNP AND PersonID <> @PersonID)
BEGIN
	SET @ErrorCode = 1
	GOTO __END
END
*/

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

IF @Operation = 0
BEGIN 
	INSERT Persons (	AddressID,
						LastName,
						FirstName,
						MaidenName,
						FatherID,
						MotherID,
						BirthDate,
						PlaceOfBirth,
						CNP,
						Series,
						SeriesNumber,
						Sex,
						Photo,
						HomePhone,
						MobilePhone,
						DeathDate,
						Details,
						ChurchID,
						ChurchName,
						BelongDate)
	VALUES(	@AddressID,
			@LastName,
			@FirstName,
			@MaidenName,
			@FatherID,
			@MotherID,
			@BirthDate,
			@PlaceOfBirth,
			@CNP,
			@Series,
			@SeriesNumber,
			@Sex,
			@Photo,
			@HomePhone,
			@MobilePhone,
			@DeathDate,
			@Details,
			@ChurchID,
			@ChurchName,
			@BelongDate)

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

IF @Operation = 1
BEGIN
	UPDATE Persons
	SET
		AddressID = @AddressID,
		LastName = @LastName,
		FirstName = @FirstName,
		MaidenName = @MaidenName,
		FatherID = @FatherID,
		MotherID = @MotherID,
		BirthDate = @BirthDate,
		PlaceOfBirth = @PlaceOfBirth,
		CNP = @CNP,
		Series = @Series,
		SeriesNumber = @SeriesNumber,
		Sex = @Sex,
		Photo = @Photo,
		HomePhone = @HomePhone,
		MobilePhone = @MobilePhone,
		DeathDate = @DeathDate,
		Details = @Details,
		ChurchID = @ChurchID,
		ChurchName = @ChurchName,
		BelongDate = @BelongDate 

	WHERE
		PersonID = @PersonID

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

END	

IF @Operation = 2
BEGIN
		-- ChurchesGroupsPersons
		IF EXISTS (SELECT CGP.PersonID FROM ChurchesGroupsPersons CGP 
			WHERE (CGP.PersonID = @PersonID))
		BEGIN
			SET @ErrorCode = 6
			GOTO __END
		END

		-- ChurchesRegistersFamilies
		IF EXISTS (SELECT CRF.MarriageID FROM ChurchesRegistersFamilies CRF 
			LEFT OUTER JOIN Marriages M ON CRF.MarriageID = M.MarriageID 
			WHERE(M.HusbandID = @PersonID OR M.WifeID = @PersonID))
		BEGIN
			SET @ErrorCode = 7
			GOTO __END
		END

		-- ChurchesRegistersPersons
		IF EXISTS (SELECT CRP.PersonID FROM ChurchesRegistersPersons CRP WHERE CRP.PersonID = @PersonID)
		BEGIN
			SET @ErrorCode = 8
			GOTO __END
		END
		
		-- ChurchesServants
		IF EXISTS (SELECT CS.ServantID FROM ChurchesServants CS
			WHERE CS.ServantID = @PersonID)
		BEGIN
			SET @ErrorCode = 9
			GOTO __END
		END
		
		-- Departures
		IF EXISTS (SELECT D.PersonID FROM Departures D
			WHERE D.PersonID = @PersonID)
		BEGIN
			SET @ErrorCode = 10
			GOTO __END
		END

		-- Marriages
		IF EXISTS (SELECT M.MarriageID FROM Marriages M
			WHERE(M.HusbandID = @PersonID OR M.WifeID = @PersonID))
		BEGIN
			SET @ErrorCode = 11
			GOTO __END
		END

		-- Persons - Father
		IF EXISTS (SELECT P.PersonID FROM Persons P
			WHERE(P.FatherID = @PersonID))
		BEGIN
			SET @ErrorCode = 12
			GOTO __END
		END
		
		-- Persons - Mother
		IF EXISTS (SELECT P.PersonID FROM Persons P
			WHERE(P.MotherID = @PersonID))
		BEGIN
			SET @ErrorCode = 13
			GOTO __END
		END
		
		-- SpecialCases
		IF EXISTS (SELECT SC.PersonID FROM SpecialCases SC
			WHERE SC.PersonID = @PersonID)
		BEGIN
			SET @ErrorCode = 14
			GOTO __END
		END
			
		-- Transfers
		IF EXISTS (SELECT T.PersonID FROM Transfers T
			WHERE T.PersonID = @PersonID)
		BEGIN
			SET @ErrorCode = 15
			GOTO __END
		END


		BEGIN TRANSACTION
		
		-- Baptisms
		Delete from Baptisms where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- Blessings
		Delete from Blessings where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- HolySpiritBaptisms
		Delete from HolySpiritBaptisms where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
			
		-- Members	
		Delete from Members where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- PersonsFinancialPositions
		Delete from PersonsFinancialPositions where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- PersonsPropertiesTypes
		Delete from PersonsPropertiesTypes where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- ChurchesServants
		Delete from ChurchesServants where ServantID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- ChurchesGroupsPersons
		Delete from ChurchesGroupsPersons where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- Marriages
		Delete from Marriages WHERE HusbandID = @PersonID OR WifeID = @PersonID 
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- Persons - Mother
		UPDATE Persons SET MotherID = NULL WHERE MotherID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- Persons - Father
		UPDATE Persons SET FatherID = NULL WHERE FatherID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK

		-- Transfers
		Delete from Transfers where PersonID = @PersonID                        
		IF @@ERROR <> 0
			GOTO __ROLLBACK
		
		-- SpecialCases
		Delete from SpecialCases where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK

		-- Persons
		Delete from Persons where PersonID = @PersonID
		IF @@ERROR <> 0
			GOTO __ROLLBACK

		-- Addresses
		IF @AddressID IS NOT NULL
		BEGIN
			Delete from Addresses where AddressID = @AddressID
			IF @@ERROR <> 0
				GOTO __ROLLBACK
		END

	COMMIT TRANSACTION 
	GOTO __END
	
	__ROLLBACK:
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SET @ErrorCode = 5
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].[PersonsJobsGet]    Script Date: 02/20/2011 00:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsJobsGet]
	@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,
		ISNULL(M.IsActive, 0) AS IsMember,
		J.JobID JobID, J.Name Job, PFP.MonthlyWage, 
		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 
	LEFT OUTER JOIN PersonsFinancialPositions PFP ON (P.PersonID = PFP.PersonID)
		LEFT OUTER JOIN Jobs J ON (PFP.JobID = J.JobID)
	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 @ChurchID = -1 OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[PersonsGet]    Script Date: 02/20/2011 00:59:49 ******/
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,
		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
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)
GO



/*****************************************************************************************************/
/******     List of relationships which exist in version 1.0.0.5 but not in version 1.0.0.4		******/
/****** 				Members																	******/
/*****************************************************************************************************/


/****** Object:  ForeignKey [FK_Members_Churches]    Script Date: 02/19/2011 17:16:42 ******/
ALTER TABLE [dbo].[Members]  WITH CHECK ADD  CONSTRAINT [FK_Members_Churches] FOREIGN KEY([ChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Members] CHECK CONSTRAINT [FK_Members_Churches]
GO


/****** Object:  ForeignKey [FK_Members_Persons]    Script Date: 02/19/2011 17:16:42 ******/
ALTER TABLE [dbo].[Members]  WITH CHECK ADD  CONSTRAINT [FK_Members_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Members] CHECK CONSTRAINT [FK_Members_Persons]
GO





/*****************************************************************************************************/
/****** 								Update Members											******/
/*****************************************************************************************************/


INSERT INTO Members(ChurchID, Church, PersonID, IsActive, BecomeDate, RetireDate, Details)
SELECT 
		P.ChurchID, 
		C.Name,
		P.PersonID,
		1,
		BaptismDate,
		null, 
		null
FROM Persons P
	INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
	INNER JOIN Churches C ON (P.ChurchID = C.ChurchID)

GO

UPDATE Members
	SET 
		IsActive = 0, 
		RetireDate = P.DeathDate
FROM Members M
	INNER JOIN Persons P ON (M.PersonID = P.PersonID) 	
WHERE P.DeathDate IS NOT NULL
	
GO