USE master
GO

CREATE DATABASE Church
GO


USE [Church]
GO
/****** Object:  Table [dbo].[Churches]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Churches](
	[ChurchID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Address] [nvarchar](256) NULL,
	[IsChild] [bit] NULL,
 CONSTRAINT [PK_Churches] PRIMARY KEY CLUSTERED 
(
	[ChurchID] 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
/****** Object:  Table [dbo].[Grids]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Grids](
	[GridID] [int] NOT NULL,
	[AutoSizeColumnsMode] [int] NOT NULL,
	[MultilineHeader] [tinyint] NULL,
 CONSTRAINT [PK_Grids] PRIMARY KEY CLUSTERED 
(
	[GridID] 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
/****** Object:  UserDefinedFunction [dbo].[fn_CalculateAge]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_CalculateAge]
(
	@BirthDate datetime,
	@CurrentDate datetime
)
RETURNS int

AS

BEGIN

IF @BirthDate > @CurrentDate
BEGIN
  DECLARE @TempDate datetime
  SELECT
    @TempDate = @BirthDate,
    @BirthDate = @CurrentDate,
    @CurrentDate = @TempDate
END

DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) - 
  CASE WHEN(
    (MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
    (MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
  ) THEN 1 ELSE 0 END
RETURN @Age

END
GO
/****** Object:  Table [dbo].[Districts]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Districts](
	[DistrictID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED 
(
	[DistrictID] 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
/****** Object:  Table [dbo].[FamiliesRegistersTypes]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FamiliesRegistersTypes](
	[RegisterTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_FamiliesRegistersTypes] PRIMARY KEY CLUSTERED 
(
	[RegisterTypeID] 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
/****** Object:  Table [dbo].[Jobs]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 
(
	[JobID] 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
/****** Object:  Table [dbo].[GroupsTypes]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupsTypes](
	[GroupTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_GroupsTypes] PRIMARY KEY CLUSTERED 
(
	[GroupTypeID] 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
/****** Object:  Table [dbo].[RegistersTypes]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RegistersTypes](
	[RegisterTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_RegisterTypes] PRIMARY KEY CLUSTERED 
(
	[RegisterTypeID] 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
/****** Object:  Table [dbo].[ServantsTitles]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServantsTitles](
	[ServantTitleID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_ServantsTitles] PRIMARY KEY CLUSTERED 
(
	[ServantTitleID] 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
/****** Object:  Table [dbo].[PropertiesTypes]    Script Date: 01/06/2011 21:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PropertiesTypes](
	[PropertyTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_PropertiesTypes] PRIMARY KEY CLUSTERED 
(
	[PropertyTypeID] 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
/****** Object:  StoredProcedure [dbo].[GroupsTypesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GroupsTypesGet]
	@GroupTypeID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		GroupTypeID,
		[Name]
				
FROM GroupsTypes
WHERE
	@GroupTypeID = -1 OR (@GroupTypeID <> -1 AND GroupTypeID = @GroupTypeID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[PropertiesTypesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PropertiesTypesGet]
	@PropertyTypeID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		PropertyTypeID,
		[Name]
				
FROM PropertiesTypes
WHERE
	@PropertyTypeID = -1 OR (@PropertyTypeID <> -1 AND PropertyTypeID = @PropertyTypeID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[JobsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[JobsGet]
	@JobID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		JobID,
		[Name]
				
FROM Jobs
WHERE
	@JobID = -1 OR (@JobID <> -1 AND JobID = @JobID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[ServantsTitlesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ServantsTitlesGet]
	@ServantTitleID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		ServantTitleID,
		[Name]
				
FROM ServantsTitles
WHERE
	@ServantTitleID = -1 OR (@ServantTitleID <> -1 AND ServantTitleID = @ServantTitleID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[RegistersTypesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RegistersTypesGet]
	@RegisterTypeID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		RegisterTypeID,
		[Name]
				
FROM RegistersTypes
WHERE
	@RegisterTypeID = -1 OR (@RegisterTypeID <> -1 AND RegisterTypeID = @RegisterTypeID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[GridsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[GridsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Table [dbo].[GridsColumns]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GridsColumns](
	[GridID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[HeaderText] [nvarchar](50) NOT NULL,
	[Visible] [bit] NOT NULL,
	[DisplayIndex] [int] NOT NULL,
	[Width] [int] NOT NULL,
 CONSTRAINT [PK_GridsColumns] PRIMARY KEY CLUSTERED 
(
	[GridID] ASC,
	[Name] 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
/****** Object:  StoredProcedure [dbo].[FamiliesRegistersTypesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FamiliesRegistersTypesGet]
	@RegisterTypeID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		RegisterTypeID,
		[Name]
				
FROM FamiliesRegistersTypes
WHERE
	@RegisterTypeID = -1 OR (@RegisterTypeID <> -1 AND RegisterTypeID = @RegisterTypeID)

ORDER BY [Name]
GO
/****** Object:  StoredProcedure [dbo].[DistrictsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DistrictsGet]
	@DistrictID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		DistrictID,
		[Name]
				
FROM Districts
WHERE
	@DistrictID = -1 OR (@DistrictID <> -1 AND DistrictID = @DistrictID)

ORDER BY [Name]
GO
/****** Object:  Table [dbo].[ChurchesProperties]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChurchesProperties](
	[ChurchPropertyID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NULL,
	[Property] [nvarchar](50) NULL,
	[Count] [int] NULL,
 CONSTRAINT [PK_ChurchesProperties] PRIMARY KEY CLUSTERED 
(
	[ChurchPropertyID] 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_ChurchesProperties] ON [dbo].[ChurchesProperties] 
(
	[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
/****** Object:  Table [dbo].[ChurchesRegistersPersons]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChurchesRegistersPersons](
	[ChurchID] [int] NOT NULL,
	[RegisterTypeID] [int] NOT NULL,
	[PersonID] [int] NOT NULL,
	[RegisterNo] [int] NULL,
	[RegisterPersonDetails] [nvarchar](256) NULL,
	[ChurchRegisterPersonID] [int] IDENTITY(1,1) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_ChurchesRegistersPersons] PRIMARY KEY CLUSTERED 
(
	[ChurchRegisterPersonID] 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_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
/****** Object:  Table [dbo].[ChurchesRegistersFamilies]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChurchesRegistersFamilies](
	[ChurchID] [int] NOT NULL,
	[RegisterTypeID] [int] NOT NULL,
	[MarriageID] [int] NOT NULL,
	[RegisterNo] [int] NULL,
	[RegisterFamilyDetails] [nvarchar](256) NULL,
	[ChurchRegisterFamilyID] [int] IDENTITY(1,1) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_ChurchesRegistersFamilies] PRIMARY KEY CLUSTERED 
(
	[ChurchRegisterFamilyID] 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_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
/****** Object:  Table [dbo].[Addresses]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Addresses](
	[AddressID] [int] IDENTITY(1,1) NOT NULL,
	[DistrictID] [int] NULL,
	[Address] [nvarchar](256) NULL,
 CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED 
(
	[AddressID] 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_Addresses] ON [dbo].[Addresses] 
(
	[DistrictID] 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
/****** Object:  StoredProcedure [dbo].[ChurchesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChurchesGet]
	@ChurchID	int = -1
AS

	/* SET NOCOUNT ON */

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

FROM Churches
WHERE
	@ChurchID = -1 OR (@ChurchID <> -1 AND ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchesByTypeGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChurchesByTypeGet]
	@IsChild	bit = false
AS

	/* SET NOCOUNT ON */

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

FROM Churches
WHERE
	IsChild = @IsChild
GO
/****** Object:  StoredProcedure [dbo].[AddressesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddressesSet]
	@Operation		int = -1,
	@AddressID		int = -1 OUTPUT,
	@DistrictID		int,
	@Address		nvarchar(256)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Addresses (DistrictID, Address)
	VALUES(@DistrictID, @Address)

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

IF @Operation = 1
BEGIN
	UPDATE Addresses
	SET
		DistrictID = @DistrictID,
		Address = @Address
	WHERE
		AddressID = @AddressID

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

END	

IF @Operation = 2
BEGIN
	DELETE Addresses
	WHERE
		AddressID = @AddressID

	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].[AddressesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddressesGet]
	@AddressID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		AddressID,
		DistrictID,
		[Address]
				
FROM Addresses
WHERE
	@AddressID = -1 OR (@AddressID <> -1 AND AddressID = @AddressID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchesPropertiesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChurchesPropertiesSet]
	@Operation			int = -1,
	@ChurchPropertyID	int = -1 OUTPUT,
	@ChurchID			int,
	@Property			nvarchar(50),
	@Count				int
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT ChurchesProperties (ChurchID, [Property], [Count])
	VALUES(@ChurchID, @Property, @Count)

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

IF @Operation = 1
BEGIN
	UPDATE ChurchesProperties
	SET
		ChurchID = @ChurchID,
		[Property] = @Property,
		[Count] = @Count
	WHERE
		ChurchPropertyID = @ChurchPropertyID

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

END	

IF @Operation = 2
BEGIN
	DELETE ChurchesProperties
	WHERE
		ChurchPropertyID = @ChurchPropertyID

	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].[ChurchesPropertiesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChurchesPropertiesGet]
	@ChurchPropertyID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		ChurchPropertyID,
		ChurchID,
		[Property],
		[Count]
		
FROM ChurchesProperties
WHERE
	@ChurchPropertyID = -1 OR (@ChurchPropertyID <> -1 AND ChurchPropertyID = @ChurchPropertyID)

ORDER BY [Property]
GO
/****** Object:  Table [dbo].[Persons]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Persons](
	[PersonID] [int] IDENTITY(1,1) NOT NULL,
	[AddressID] [int] NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[MaidenName] [nvarchar](50) NULL,
	[FatherID] [int] NULL,
	[MotherID] [int] NULL,
	[BirthDate] [datetime] NULL,
	[PlaceOfBirth] [nvarchar](50) NULL,
	[CNP] [nvarchar](50) NULL,
	[Series] [nvarchar](50) NULL,
	[SeriesNumber] [nvarchar](50) NULL,
	[Sex] [bit] NOT NULL,
	[Photo] [varbinary](max) NULL,
	[HomePhone] [nvarchar](50) NULL,
	[MobilePhone] [nvarchar](50) NULL,
	[DeathDate] [datetime] NULL,
	[Details] [nvarchar](256) NULL,
	[ChurchID] [int] NULL,
	[ChurchName] [nvarchar](50) NULL,
	[BelongDate] [datetime] NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_Persons] ON [dbo].[Persons] 
(
	[AddressID] 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_Persons_1] ON [dbo].[Persons] 
(
	[FatherID] 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_Persons_2] ON [dbo].[Persons] 
(
	[MotherID] 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_Persons_3] ON [dbo].[Persons] 
(
	[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
/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[DistrictsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DistrictsSet]
	@Operation		int = -1,
	@DistrictID		int = -1 OUTPUT,
	@Name			nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Districts ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE Districts
	SET
		[Name] = @Name
	WHERE
		DistrictID = @DistrictID

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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT A.DistrictID FROM Addresses A
		WHERE(A.DistrictID = @DistrictID))
	BEGIN
		SET @ErrorCode = 6
		GOTO __END
	END

	DELETE Districts
	WHERE
		DistrictID = @DistrictID

	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].[ChurchRegistersFamiliesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[GridsColumnsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsColumnsSet]
	@Operation				int = -1,
	@GridID					int,
	@Name					nvarchar(50),
	@HeaderText				nvarchar(50),
	@Visible				bit,
	@DisplayIndex			int,
	@Width					int
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT GridsColumns (GridID, [Name], HeaderText, Visible, DisplayIndex, Width)
	VALUES(@GridID, @Name, @HeaderText, @Visible, @DisplayIndex, @Width)

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

IF @Operation = 1
BEGIN
	UPDATE GridsColumns
	SET
		HeaderText = @HeaderText,
		Visible = @Visible, 
		DisplayIndex = @DisplayIndex, 
		Width = @Width
	WHERE
		GridID = @GridID AND [Name] = @Name

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

END	

IF @Operation = 2
BEGIN

	DELETE GridsColumns
	WHERE
		GridID = @GridID AND [Name] = @Name

	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].[GridsColumnsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsColumnsGet]
	@GridID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT
		GridID,
		[Name],
		HeaderText,
		Visible,
		DisplayIndex,
		Width
				
FROM GridsColumns
WHERE
	@GridID = -1 OR (@GridID <> -1 AND GridID = @GridID)

ORDER BY DisplayIndex
GO
/****** Object:  StoredProcedure [dbo].[RegistersTypesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RegistersTypesSet]
	@Operation		int = -1,
	@RegisterTypeID	int = -1 OUTPUT,
	@Name			nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT RegistersTypes ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE RegistersTypes
	SET
		[Name] = @Name
	WHERE
		RegisterTypeID = @RegisterTypeID

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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT CRP.RegisterTypeID FROM ChurchesRegistersPersons CRP
		WHERE(CRP.RegisterTypeID = @RegisterTypeID))
	BEGIN
		SET @ErrorCode = 6
		GOTO __END
	END

	DELETE RegistersTypes
	WHERE
		RegisterTypeID = @RegisterTypeID

	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].[MaxPersonsRegisterNoGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[FamiliesRegistersTypesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FamiliesRegistersTypesSet]
	@Operation		int = -1,
	@RegisterTypeID	int = -1 OUTPUT,
	@Name			nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT FamiliesRegistersTypes ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE FamiliesRegistersTypes
	SET
		[Name] = @Name
	WHERE
		RegisterTypeID = @RegisterTypeID

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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT CRF.RegisterTypeID FROM ChurchesRegistersFamilies CRF
		WHERE(CRF.RegisterTypeID = @RegisterTypeID))
	BEGIN
		SET @ErrorCode = 6
		GOTO __END
	END

	DELETE FamiliesRegistersTypes
	WHERE
		RegisterTypeID = @RegisterTypeID

	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:  Table [dbo].[PersonsPropertiesTypes]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonsPropertiesTypes](
	[PersonPropertyTypeID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[PropertyTypeID] [int] NULL,
	[Count] [int] NULL,
 CONSTRAINT [PK_PersonsPropertiesTypes] PRIMARY KEY CLUSTERED 
(
	[PersonPropertyTypeID] 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_PersonsPropertiesTypes] ON [dbo].[PersonsPropertiesTypes] 
(
	[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_PersonsPropertiesTypes_1] ON [dbo].[PersonsPropertiesTypes] 
(
	[PropertyTypeID] 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
/****** Object:  StoredProcedure [dbo].[MotherByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MotherByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */

SELECT PM.PersonID PersonID, PM.FirstName FirstName, PM.LastName LastName, PM.Sex
FROM Persons P
	INNER JOIN Persons PM ON (P.MotherID = PM.PersonID)
WHERE P.PersonID = @PersonID AND P.MotherID IS NOT NULL
GO
/****** Object:  Table [dbo].[PersonsFinancialPositions]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonsFinancialPositions](
	[PersonFinancialPositionID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[JobID] [int] NULL,
	[MonthlyWage] [int] NULL,
 CONSTRAINT [PK_PersonsFinancialPositions] PRIMARY KEY CLUSTERED 
(
	[PersonFinancialPositionID] 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_PersonsFinancialPositions] ON [dbo].[PersonsFinancialPositions] 
(
	[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_PersonsFinancialPositions_1] ON [dbo].[PersonsFinancialPositions] 
(
	[JobID] 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
/****** Object:  StoredProcedure [dbo].[PersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Table [dbo].[Transfers]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transfers](
	[TransferID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[TransferredFromChurchID] [int] NULL,
	[TransferredFromChurchName] [nvarchar](50) NULL,
	[TransferredToChurchID] [int] NULL,
	[TransferredToChurchName] [nvarchar](50) NULL,
	[TransferDate] [datetime] NULL,
	[TransferAct] [nvarchar](50) NULL,
	[TransferDetails] [nvarchar](256) NULL,
 CONSTRAINT [PK_Transfers] PRIMARY KEY CLUSTERED 
(
	[TransferID] 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_Transfers] ON [dbo].[Transfers] 
(
	[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_Transfers_1] ON [dbo].[Transfers] 
(
	[TransferredFromChurchID] 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_Transfers_2] ON [dbo].[Transfers] 
(
	[TransferredToChurchID] 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
/****** Object:  StoredProcedure [dbo].[TransferredPersonsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Table [dbo].[SpecialCases]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SpecialCases](
	[SpecialCaseID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NULL,
	[PersonID] [int] NULL,
	[IsActive] [bit] NOT NULL,
	[SpecialCaseDate] [datetime] NULL,
	[SolvedDate] [datetime] NULL,
	[SpecialCaseDetails] [nvarchar](1024) NULL,
 CONSTRAINT [PK_SpecialCases] PRIMARY KEY CLUSTERED 
(
	[SpecialCaseID] 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_SpecialCases] ON [dbo].[SpecialCases] 
(
	[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_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
/****** Object:  Table [dbo].[HolySpiritBaptisms]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HolySpiritBaptisms](
	[HolySpiritBaptismID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[HolySpiritBaptismAddress] [nvarchar](256) NULL,
	[HolySpiritBaptismDate] [datetime] NULL,
 CONSTRAINT [PK_HolySpiritBaptisms] PRIMARY KEY CLUSTERED 
(
	[HolySpiritBaptismID] 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_HolySpiritBaptisms] ON [dbo].[HolySpiritBaptisms] 
(
	[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
/****** Object:  Table [dbo].[ChurchesServants]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChurchesServants](
	[ChurchServantID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NULL,
	[ServantID] [int] NULL,
	[ServantTitleID] [int] NULL,
	[IsActive] [bit] NOT NULL,
	[ServantBecomeDate] [datetime] NULL,
	[ServantRetireDate] [datetime] NULL,
	[ServantDetails] [nvarchar](1024) NULL,
 CONSTRAINT [PK_ChurchesServants] PRIMARY KEY CLUSTERED 
(
	[ChurchServantID] 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_ChurchesServants] ON [dbo].[ChurchesServants] 
(
	[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_ChurchesServants_1] ON [dbo].[ChurchesServants] 
(
	[ServantID] 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_ChurchesServants_2] ON [dbo].[ChurchesServants] 
(
	[ServantTitleID] 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
/****** Object:  StoredProcedure [dbo].[ChurchPersonsBySexGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

CREATE 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].[ChurchPersonsAllGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Table [dbo].[Departures]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Departures](
	[DepartureID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NULL,
	[PersonID] [int] NULL,
	[IsActive] [bit] NOT NULL,
	[DepartureDate] [datetime] NULL,
	[ReturnDate] [datetime] NULL,
	[DepartureAddress] [nvarchar](256) NULL,
	[DepartureDetails] [nvarchar](256) NULL,
 CONSTRAINT [PK_Departures] PRIMARY KEY CLUSTERED 
(
	[DepartureID] 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_Departures] ON [dbo].[Departures] 
(
	[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_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
/****** Object:  Table [dbo].[Members]    Script Date: 01/06/2011 21:11:34 ******/
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
/****** Object:  Table [dbo].[Marriages]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Marriages](
	[MarriageID] [int] IDENTITY(1,1) NOT NULL,
	[Divorce] [bit] NULL,
	[HusbandID] [int] NULL,
	[WifeID] [int] NULL,
	[CivilServiceDate] [datetime] NULL,
	[ReligiousServiceDate] [datetime] NULL,
	[WeddingChurchID] [int] NULL,
	[WeddingChurch] [nvarchar](50) NULL,
	[WeddingServants] [nvarchar](256) NULL,
 CONSTRAINT [PK_Marriages] PRIMARY KEY CLUSTERED 
(
	[MarriageID] 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_Marriages] ON [dbo].[Marriages] 
(
	[HusbandID] 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_Marriages_1] ON [dbo].[Marriages] 
(
	[WifeID] 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_Marriages_2] ON [dbo].[Marriages] 
(
	[WeddingChurchID] 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
/****** Object:  StoredProcedure [dbo].[FatherByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FatherByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */

SELECT PF.PersonID PersonID, PF.FirstName FirstName, PF.LastName LastName, PF.Sex
FROM Persons P
	INNER JOIN Persons PF ON (P.FatherID = PF.PersonID)
WHERE P.PersonID = @PersonID AND P.FatherID IS NOT NULL
GO
/****** Object:  Table [dbo].[ChurchesGroupsPersons]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChurchesGroupsPersons](
	[ChurchGroupPersonID] [int] IDENTITY(1,1) NOT NULL,
	[ChurchID] [int] NOT NULL,
	[GroupTypeID] [int] NOT NULL,
	[PersonID] [int] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[ComingIntoGroupDate] [datetime] NULL,
	[LeavingGroupDate] [datetime] NULL,
	[GroupPersonDetails] [nvarchar](256) NULL,
 CONSTRAINT [PK_ChurchesGroupsPersons] PRIMARY KEY CLUSTERED 
(
	[ChurchGroupPersonID] 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_ChurchesGroupsPersons] ON [dbo].[ChurchesGroupsPersons] 
(
	[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_ChurchesGroupsPersons_1] ON [dbo].[ChurchesGroupsPersons] 
(
	[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_ChurchesGroupsPersons_2] ON [dbo].[ChurchesGroupsPersons] 
(
	[GroupTypeID] 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
/****** Object:  StoredProcedure [dbo].[AddressByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddressByPersonGet]
	@PersonID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT A.* 
FROM Addresses A
	LEFT OUTER JOIN Persons P ON (A.AddressID = P.AddressID)
WHERE P.PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[ChildrenSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChildrenByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Table [dbo].[Baptisms]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Baptisms](
	[BaptismID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[BaptismChurchID] [int] NULL,
	[BaptismChurch] [nvarchar](50) NULL,
	[BaptismDate] [datetime] NULL,
	[BaptismServant] [nvarchar](50) NULL,
 CONSTRAINT [PK_Baptisms] PRIMARY KEY CLUSTERED 
(
	[BaptismID] 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_Baptisms] ON [dbo].[Baptisms] 
(
	[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_Baptisms_1] ON [dbo].[Baptisms] 
(
	[BaptismChurchID] 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
/****** Object:  Table [dbo].[Blessings]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blessings](
	[BlessingID] [int] IDENTITY(1,1) NOT NULL,
	[PersonID] [int] NULL,
	[BlessingChurchID] [int] NULL,
	[BlessingChurch] [nvarchar](50) NULL,
	[BlessingServant] [nvarchar](50) NULL,
	[BlessingDate] [datetime] NULL,
 CONSTRAINT [PK_Blessings] PRIMARY KEY CLUSTERED 
(
	[BlessingID] 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_Blessings] ON [dbo].[Blessings] 
(
	[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_Blessings_1] ON [dbo].[Blessings] 
(
	[BlessingChurchID] 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
/****** Object:  StoredProcedure [dbo].[BlessingByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[BlessedPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[BlessedByMariageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[BaptizedPersonsNotBelongToChurchGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[BaptizedPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[BaptizedByMariageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[BaptismsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BaptismsSet]
	@Operation			int = -1,
	@BaptismID			int = -1 OUTPUT,
	@PersonID			int,
	@BaptismChurchID	int,
	@BaptismChurch		nvarchar(50),
	@BaptismServant		nvarchar(50),
	@BaptismDate		datetime
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Baptisms (PersonID, BaptismChurchID, BaptismChurch, BaptismServant, BaptismDate)
	VALUES(@PersonID, @BaptismChurchID, @BaptismChurch, @BaptismServant, @BaptismDate)

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

IF @Operation = 1
BEGIN
	UPDATE Baptisms
	SET
		PersonID = @PersonID,
		BaptismChurch = @BaptismChurch,
		BaptismChurchID = @BaptismChurchID,
		BaptismServant = @BaptismServant,
		BaptismDate = @BaptismDate
	WHERE
		BaptismID = @BaptismID

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

END	

IF @Operation = 2
BEGIN
	DELETE Baptisms
	WHERE
		BaptismID = @BaptismID

	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].[BaptismsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[BaptismPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[BaptismByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChildrenByMariageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[BlessingsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BlessingsSet]
	@Operation			int = -1,
	@BlessingID			int = -1 OUTPUT,
	@PersonID			int,
	@BlessingChurchID	int,
	@BlessingChurch		nvarchar(50),
	@BlessingServant	nvarchar(50),
	@BlessingDate		datetime
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Blessings (PersonID, BlessingChurchID, BlessingChurch, BlessingServant, BlessingDate)
	VALUES(@PersonID, @BlessingChurchID, @BlessingChurch, @BlessingServant, @BlessingDate)

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

IF @Operation = 1
BEGIN
	UPDATE Blessings
	SET
		PersonID = @PersonID,
		BlessingChurch = @BlessingChurch,
		BlessingChurchID = @BlessingChurchID,
		BlessingServant = @BlessingServant,
		BlessingDate = @BlessingDate
	WHERE
		BlessingID = @BlessingID

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

END	

IF @Operation = 2
BEGIN
	DELETE Blessings
	WHERE
		BlessingID = @BlessingID

	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].[PersonPropertiesTypesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PersonPropertiesTypesGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT PHT.PersonPropertyTypeID PersonPropertyTypeID, PHT.PersonID PersonID, PHT.PropertyTypeID PropertyTypeID, HT.Name Name, PHT.Count Count
FROM PersonsPropertiesTypes PHT
	INNER JOIN PropertiesTypes HT ON (HT.PropertyTypeID = PHT.PropertyTypeID)
WHERE PHT.PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[MarriageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[MarriageByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[JobsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[JobsSet]
	@Operation		int = -1,
	@JobID			int = -1 OUTPUT,
	@Name			nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Jobs ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE Jobs
	SET
		[Name] = @Name
	WHERE
		JobID = @JobID

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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT PFP.JobID FROM PersonsFinancialPositions PFP
		WHERE(PFP.JobID = @JobID))
	BEGIN
		SET @ErrorCode = 6
		GOTO __END
	END


	DELETE Jobs
	WHERE
		JobID = @JobID

	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].[FamiliesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[FamiliesByChurchGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchBaptizedPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchRegistersPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[DeparturesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[DepartedPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchRegistersFamiliesGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchesServantsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchMembersGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchGroupsPersonsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChurchGroupsPersonsSet]
	@Operation					int = -1,
	@ChurchGroupPersonID		int = -1 OUTPUT,
	@ChurchID					int,
	@GroupTypeID				int,
	@PersonID					int,
	@IsActive					bit,
	@ComingIntoGroupDate		datetime,
	@LeavingGroupDate			datetime,
	@GroupPersonDetails				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 ChurchesGroupsPersons 
		WHERE ChurchGroupPersonID = @ChurchGroupPersonID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END

IF @Operation = 0
BEGIN 
	INSERT ChurchesGroupsPersons (ChurchID, GroupTypeID, PersonID, IsActive, ComingIntoGroupDate, LeavingGroupDate, GroupPersonDetails)
	VALUES(@ChurchID, @GroupTypeID, @PersonID, @IsActive, @ComingIntoGroupDate, @LeavingGroupDate, @GroupPersonDetails)

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

END

IF @Operation = 1
BEGIN
	UPDATE ChurchesGroupsPersons
	SET
		ChurchID = @ChurchID,
		GroupTypeID = @GroupTypeID,
		PersonID = @PersonID,
		IsActive = @IsActive,
		ComingIntoGroupDate = @ComingIntoGroupDate,
		LeavingGroupDate = @LeavingGroupDate,
		GroupPersonDetails = @GroupPersonDetails

	WHERE ChurchGroupPersonID = @ChurchGroupPersonID

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

END	

IF @Operation = 2
BEGIN
	DELETE ChurchesGroupsPersons
	WHERE ChurchGroupPersonID = @ChurchGroupPersonID

	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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HolySpiritBaptismByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT HolySpiritBaptismID, PersonID, HolySpiritBaptismDate, HolySpiritBaptismAddress 
FROM HolySpiritBaptisms
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[GroupsTypesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GroupsTypesSet]
	@Operation		int = -1,
	@GroupTypeID	int = -1 OUTPUT,
	@Name			nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT GroupsTypes ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE GroupsTypes
	SET
		[Name] = @Name
	WHERE
		GroupTypeID = @GroupTypeID

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

END	

IF @Operation = 2
BEGIN

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

	DELETE GroupsTypes
	WHERE
		GroupTypeID = @GroupTypeID

	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:  UserDefinedFunction [dbo].[IsBaptized]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IsBaptized] (@PersonID int)
RETURNS bit
AS
BEGIN
declare @Return bit

IF EXISTS(SELECT * FROM Baptisms WHERE PersonID = @PersonID)
	SET @Return = 1
ELSE 
	SET @Return = 0
	
RETURN (@Return) 

END
GO
/****** Object:  StoredProcedure [dbo].[UnBaptizedPersonsGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ServantsTitlesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ServantsTitlesSet]
	@Operation			int = -1,
	@ServantTitleID		int = -1 OUTPUT,
	@Name				nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT ServantsTitles ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE ServantsTitles
	SET
		[Name] = @Name
	WHERE
		ServantTitleID = @ServantTitleID

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

END	

IF @Operation = 2
BEGIN

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


	DELETE ServantsTitles
	WHERE
		ServantTitleID = @ServantTitleID

	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].[SpecialCasesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[PropertiesTypesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PropertiesTypesSet]
	@Operation			int = -1,
	@PropertyTypeID		int = -1 OUTPUT,
	@Name				nvarchar(50)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT PropertiesTypes ([Name])
	VALUES(@Name)

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

IF @Operation = 1
BEGIN
	UPDATE PropertiesTypes
	SET
		[Name] = @Name
	WHERE
		PropertyTypeID = @PropertyTypeID

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

END	

IF @Operation = 2
BEGIN

	IF EXISTS (SELECT PPT.PropertyTypeID FROM PersonsPropertiesTypes PPT
		WHERE(PPT.PropertyTypeID = @PropertyTypeID))
	BEGIN
		SET @ErrorCode = 6
		GOTO __END
	END

	DELETE PropertiesTypes
	WHERE
		PropertyTypeID = @PropertyTypeID

	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].[MarriagesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MarriagesSet]
	@Operation				int = -1,
	@MarriageID				int = -1 OUTPUT,
	@Divorce				bit,
	@HusbandID				int,
	@WifeID					int,
	@CivilServiceDate		datetime,
	@ReligiousServiceDate	datetime,
	@WeddingChurchID		int,
	@WeddingChurch			nvarchar(50),
	@WeddingServants		nvarchar(256)
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

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

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

IF @Operation = 1
BEGIN
	UPDATE Marriages
	SET
		Divorce = @Divorce,
		HusbandID = @HusbandID,
		WifeID = @WifeID,
		CivilServiceDate = @CivilServiceDate,
		ReligiousServiceDate = @ReligiousServiceDate,
		WeddingChurchID = @WeddingChurchID,
		WeddingChurch = @WeddingChurch,
		WeddingServants = @WeddingServants

	WHERE
		MarriageID = @MarriageID

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

END	

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

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

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

RETURN
GO
/****** Object:  StoredProcedure [dbo].[PersonFinancialPositionsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PersonFinancialPositionsSet]
	@Operation					int = -1,
	@PersonFinancialPositionID	int = -1 OUTPUT,
	@PersonID					int,	
	@JobID						int,	
	@MonthlyWage				int	

AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT PersonsFinancialPositions (PersonID, JobID, MonthlyWage)
	VALUES(@PersonID, @JobID, @MonthlyWage)
	

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

IF @Operation = 1
BEGIN
	UPDATE PersonsFinancialPositions
	SET
		PersonID = @PersonID,
		JobID = @JobID,
		MonthlyWage = @MonthlyWage
	WHERE
		PersonFinancialPositionID = @PersonFinancialPositionID

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

END	

IF @Operation = 2
BEGIN
	DELETE PersonsFinancialPositions
	WHERE
		PersonFinancialPositionID = @PersonFinancialPositionID

	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].[PersonFinancialPositionGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PersonFinancialPositionGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT * FROM PersonsFinancialPositions
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[ParentsGet]    Script Date: 01/06/2011 21:11:34 ******/
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
/****** Object:  StoredProcedure [dbo].[PersonsByPhotoGet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[MembersSet]    Script Date: 01/06/2011 21:11:34 ******/
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].[MembersByPersonGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[MembersByMariageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[MembersByChurchGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[HolySpiritBaptizedByMariageGet]    Script Date: 01/06/2011 21:11:34 ******/
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].[HolySpiritBaptismsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HolySpiritBaptismsSet]
	@Operation					int = -1,
	@HolySpiritBaptismID		int = -1 OUTPUT,
	@PersonID					int,
	@HolySpiritBaptismAddress	nvarchar(256),
	@HolySpiritBaptismDate		datetime
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT HolySpiritBaptisms (PersonID, HolySpiritBaptismAddress, HolySpiritBaptismDate)
	VALUES(@PersonID, @HolySpiritBaptismAddress, @HolySpiritBaptismDate)

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

IF @Operation = 1
BEGIN
	UPDATE HolySpiritBaptisms
	SET
		PersonID = @PersonID,
		HolySpiritBaptismDate = @HolySpiritBaptismDate,
		HolySpiritBaptismAddress = @HolySpiritBaptismAddress
	WHERE
		HolySpiritBaptismID = @HolySpiritBaptismID

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

END	

IF @Operation = 2
BEGIN
	DELETE HolySpiritBaptisms
	WHERE
		HolySpiritBaptismID = @HolySpiritBaptismID

	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].[PersonsSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[PersonsPropertiesTypesSet]    Script Date: 01/06/2011 21:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PersonsPropertiesTypesSet]
	@Operation					int = -1,
	@PersonPropertyTypeID		int = -1 OUTPUT,
	@PersonID					int,	
	@PropertyTypeID				int,	
	@Count						int	

AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT PersonsPropertiesTypes (PersonID, PropertyTypeID, Count)
	VALUES(@PersonID, @PropertyTypeID, @Count)
	

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

IF @Operation = 1
BEGIN
	UPDATE PersonsPropertiesTypes
	SET
		PersonID = @PersonID,
		PropertyTypeID = @PropertyTypeID,
		Count = @Count
	WHERE
		PersonPropertyTypeID = @PersonPropertyTypeID

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

END	

IF @Operation = 2
BEGIN
	DELETE PersonsPropertiesTypes
	WHERE
		PersonPropertyTypeID = @PersonPropertyTypeID

	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:  View [dbo].[PersonsPropertiesTypesDetails]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PersonsPropertiesTypesDetails]
AS
SELECT dbo.PersonsPropertiesTypes.PersonPropertyTypeID, dbo.PersonsPropertiesTypes.PersonID, 
                      dbo.PersonsPropertiesTypes.PropertyTypeID, dbo.PersonsPropertiesTypes.Count, dbo.PropertiesTypes.Name
FROM      dbo.PersonsPropertiesTypes INNER JOIN
                      dbo.PropertiesTypes ON dbo.PersonsPropertiesTypes.PropertyTypeID = dbo.PropertiesTypes.PropertyTypeID
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "PersonsPropertiesTypes"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 123
               Right = 237
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PropertiesTypes"
            Begin Extent = 
               Top = 11
               Left = 280
               Bottom = 98
               Right = 446
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PersonsPropertiesTypesDetails'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PersonsPropertiesTypesDetails'
GO
/****** Object:  StoredProcedure [dbo].[PersonsJobsGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

CREATE 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
/****** Object:  StoredProcedure [dbo].[HolySpiritUnBaptizedPersonsGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[HolySpiritBaptizedPersonsGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchGroupsPersonsGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[FamilyChildrenGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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].[ChurchChildrenGet]    Script Date: 01/06/2011 21:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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:  Default [DF__ChurchesR__IsAct__5CA1C101]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesRegistersPersons] ADD  DEFAULT ((1)) FOR [IsActive]
GO
/****** Object:  Default [DF__ChurchesR__IsAct__607251E5]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesRegistersFamilies] ADD  DEFAULT ((1)) FOR [IsActive]
GO
/****** Object:  ForeignKey [FK_GridsColumns_Grids]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[GridsColumns]  WITH CHECK ADD  CONSTRAINT [FK_GridsColumns_Grids] FOREIGN KEY([GridID])
REFERENCES [dbo].[Grids] ([GridID])
GO
ALTER TABLE [dbo].[GridsColumns] CHECK CONSTRAINT [FK_GridsColumns_Grids]
GO
/****** Object:  ForeignKey [FK_ChurchesProperties_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesProperties]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesProperties_Churches] FOREIGN KEY([ChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[ChurchesProperties] CHECK CONSTRAINT [FK_ChurchesProperties_Churches]
GO
/****** Object:  ForeignKey [FK_ChurchesRegistersPersons_RegistersTypes]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesRegistersPersons]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesRegistersPersons_RegistersTypes] FOREIGN KEY([RegisterTypeID])
REFERENCES [dbo].[RegistersTypes] ([RegisterTypeID])
GO
ALTER TABLE [dbo].[ChurchesRegistersPersons] CHECK CONSTRAINT [FK_ChurchesRegistersPersons_RegistersTypes]
GO
/****** Object:  ForeignKey [FK_ChurchesRegistersFamilies_FamiliesRegistersTypes]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesRegistersFamilies]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesRegistersFamilies_FamiliesRegistersTypes] FOREIGN KEY([RegisterTypeID])
REFERENCES [dbo].[FamiliesRegistersTypes] ([RegisterTypeID])
GO
ALTER TABLE [dbo].[ChurchesRegistersFamilies] CHECK CONSTRAINT [FK_ChurchesRegistersFamilies_FamiliesRegistersTypes]
GO
/****** Object:  ForeignKey [FK_Addresses_Districts]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_Districts] FOREIGN KEY([DistrictID])
REFERENCES [dbo].[Districts] ([DistrictID])
GO
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Districts]
GO
/****** Object:  ForeignKey [FK_Persons_Addresses]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Persons]  WITH CHECK ADD  CONSTRAINT [FK_Persons_Addresses] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Addresses] ([AddressID])
GO
ALTER TABLE [dbo].[Persons] CHECK CONSTRAINT [FK_Persons_Addresses]
GO
/****** Object:  ForeignKey [FK_Persons_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Persons]  WITH CHECK ADD  CONSTRAINT [FK_Persons_Churches] FOREIGN KEY([ChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Persons] CHECK CONSTRAINT [FK_Persons_Churches]
GO
/****** Object:  ForeignKey [FK_Persons_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Persons]  WITH CHECK ADD  CONSTRAINT [FK_Persons_Persons] FOREIGN KEY([FatherID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Persons] CHECK CONSTRAINT [FK_Persons_Persons]
GO
/****** Object:  ForeignKey [FK_Persons_Persons1]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Persons]  WITH CHECK ADD  CONSTRAINT [FK_Persons_Persons1] FOREIGN KEY([MotherID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Persons] CHECK CONSTRAINT [FK_Persons_Persons1]
GO
/****** Object:  ForeignKey [FK_PersonsPropertiesTypes_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[PersonsPropertiesTypes]  WITH CHECK ADD  CONSTRAINT [FK_PersonsPropertiesTypes_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[PersonsPropertiesTypes] CHECK CONSTRAINT [FK_PersonsPropertiesTypes_Persons]
GO
/****** Object:  ForeignKey [FK_PersonsPropertiesTypes_PropertiesTypes]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[PersonsPropertiesTypes]  WITH CHECK ADD  CONSTRAINT [FK_PersonsPropertiesTypes_PropertiesTypes] FOREIGN KEY([PropertyTypeID])
REFERENCES [dbo].[PropertiesTypes] ([PropertyTypeID])
GO
ALTER TABLE [dbo].[PersonsPropertiesTypes] CHECK CONSTRAINT [FK_PersonsPropertiesTypes_PropertiesTypes]
GO
/****** Object:  ForeignKey [FK_PersonsFinancialPositions_Jobs]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[PersonsFinancialPositions]  WITH CHECK ADD  CONSTRAINT [FK_PersonsFinancialPositions_Jobs] FOREIGN KEY([JobID])
REFERENCES [dbo].[Jobs] ([JobID])
GO
ALTER TABLE [dbo].[PersonsFinancialPositions] CHECK CONSTRAINT [FK_PersonsFinancialPositions_Jobs]
GO
/****** Object:  ForeignKey [FK_PersonsFinancialPositions_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[PersonsFinancialPositions]  WITH CHECK ADD  CONSTRAINT [FK_PersonsFinancialPositions_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[PersonsFinancialPositions] CHECK CONSTRAINT [FK_PersonsFinancialPositions_Persons]
GO
/****** Object:  ForeignKey [FK_Transfers_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Transfers]  WITH CHECK ADD  CONSTRAINT [FK_Transfers_Churches] FOREIGN KEY([TransferredFromChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Transfers] CHECK CONSTRAINT [FK_Transfers_Churches]
GO
/****** Object:  ForeignKey [FK_Transfers_Churches1]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Transfers]  WITH CHECK ADD  CONSTRAINT [FK_Transfers_Churches1] FOREIGN KEY([TransferredToChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Transfers] CHECK CONSTRAINT [FK_Transfers_Churches1]
GO
/****** Object:  ForeignKey [FK_Transfers_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Transfers]  WITH CHECK ADD  CONSTRAINT [FK_Transfers_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Transfers] CHECK CONSTRAINT [FK_Transfers_Persons]
GO
/****** Object:  ForeignKey [FK_SpecialCases_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[SpecialCases]  WITH CHECK ADD  CONSTRAINT [FK_SpecialCases_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[SpecialCases] CHECK CONSTRAINT [FK_SpecialCases_Persons]
GO
/****** Object:  ForeignKey [FK_HolySpiritBaptisms_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[HolySpiritBaptisms]  WITH CHECK ADD  CONSTRAINT [FK_HolySpiritBaptisms_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[HolySpiritBaptisms] CHECK CONSTRAINT [FK_HolySpiritBaptisms_Persons]
GO
/****** Object:  ForeignKey [FK_ChurchesServants_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesServants]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesServants_Churches] FOREIGN KEY([ChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[ChurchesServants] CHECK CONSTRAINT [FK_ChurchesServants_Churches]
GO
/****** Object:  ForeignKey [FK_ChurchesServants_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesServants]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesServants_Persons] FOREIGN KEY([ServantID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[ChurchesServants] CHECK CONSTRAINT [FK_ChurchesServants_Persons]
GO
/****** Object:  ForeignKey [FK_ChurchesServants_ServantsTitles]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesServants]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesServants_ServantsTitles] FOREIGN KEY([ServantTitleID])
REFERENCES [dbo].[ServantsTitles] ([ServantTitleID])
GO
ALTER TABLE [dbo].[ChurchesServants] CHECK CONSTRAINT [FK_ChurchesServants_ServantsTitles]
GO
/****** Object:  ForeignKey [FK_Departures_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Departures]  WITH CHECK ADD  CONSTRAINT [FK_Departures_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Departures] CHECK CONSTRAINT [FK_Departures_Persons]
GO
/****** Object:  ForeignKey [FK_Members_Churches]    Script Date: 01/06/2011 21:11:34 ******/
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: 01/06/2011 21:11:34 ******/
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
/****** Object:  ForeignKey [FK_Marriages_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Marriages]  WITH CHECK ADD  CONSTRAINT [FK_Marriages_Churches] FOREIGN KEY([WeddingChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Marriages] CHECK CONSTRAINT [FK_Marriages_Churches]
GO
/****** Object:  ForeignKey [FK_Marriages_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Marriages]  WITH CHECK ADD  CONSTRAINT [FK_Marriages_Persons] FOREIGN KEY([HusbandID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Marriages] CHECK CONSTRAINT [FK_Marriages_Persons]
GO
/****** Object:  ForeignKey [FK_Marriages_Persons1]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Marriages]  WITH CHECK ADD  CONSTRAINT [FK_Marriages_Persons1] FOREIGN KEY([WifeID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Marriages] CHECK CONSTRAINT [FK_Marriages_Persons1]
GO
/****** Object:  ForeignKey [FK_ChurchesGroupsPersons_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesGroupsPersons]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesGroupsPersons_Churches] FOREIGN KEY([ChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[ChurchesGroupsPersons] CHECK CONSTRAINT [FK_ChurchesGroupsPersons_Churches]
GO
/****** Object:  ForeignKey [FK_ChurchesGroupsPersons_GroupsTypes]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesGroupsPersons]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesGroupsPersons_GroupsTypes] FOREIGN KEY([GroupTypeID])
REFERENCES [dbo].[GroupsTypes] ([GroupTypeID])
GO
ALTER TABLE [dbo].[ChurchesGroupsPersons] CHECK CONSTRAINT [FK_ChurchesGroupsPersons_GroupsTypes]
GO
/****** Object:  ForeignKey [FK_ChurchesGroupsPersons_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[ChurchesGroupsPersons]  WITH CHECK ADD  CONSTRAINT [FK_ChurchesGroupsPersons_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[ChurchesGroupsPersons] CHECK CONSTRAINT [FK_ChurchesGroupsPersons_Persons]
GO
/****** Object:  ForeignKey [FK_Baptisms_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Baptisms]  WITH CHECK ADD  CONSTRAINT [FK_Baptisms_Churches] FOREIGN KEY([BaptismChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Baptisms] CHECK CONSTRAINT [FK_Baptisms_Churches]
GO
/****** Object:  ForeignKey [FK_Baptisms_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Baptisms]  WITH CHECK ADD  CONSTRAINT [FK_Baptisms_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Baptisms] CHECK CONSTRAINT [FK_Baptisms_Persons]
GO
/****** Object:  ForeignKey [FK_Blessings_Churches]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Blessings]  WITH CHECK ADD  CONSTRAINT [FK_Blessings_Churches] FOREIGN KEY([BlessingChurchID])
REFERENCES [dbo].[Churches] ([ChurchID])
GO
ALTER TABLE [dbo].[Blessings] CHECK CONSTRAINT [FK_Blessings_Churches]
GO
/****** Object:  ForeignKey [FK_Blessings_Persons]    Script Date: 01/06/2011 21:11:34 ******/
ALTER TABLE [dbo].[Blessings]  WITH CHECK ADD  CONSTRAINT [FK_Blessings_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Blessings] CHECK CONSTRAINT [FK_Blessings_Persons]
GO
