USE [Church]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_CalculateAge]    Script Date: 03/31/2010 21:30:47 ******/
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:  StoredProcedure [dbo].[ServantsTitlesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[PropertiesTypesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[FamiliesRegistersTypesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[GroupsTypesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[DistrictsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[ChurchesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesByTypeGet]
	@IsChild	bit = false
AS

	/* SET NOCOUNT ON */

SELECT
	ChurchID,
	[Name],
	Address,
	IsChild

FROM Churches
WHERE
	IsChild = @IsChild
GO
/****** Object:  StoredProcedure [dbo].[AddressesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[ChurchRegistersFamiliesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersFamiliesSet]
	@Operation				int = -1,
	@ChurchID				int,
	@RegisterTypeID			int,
	@MarriageID				int,
	@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 ChurchID = @ChurchID AND RegisterTypeID = @RegisterTypeID AND MarriageID = @MarriageID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END

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

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

--IF @Operation = 1
--BEGIN
--	UPDATE ChurchesGroupsPersons
--	SET
--		ChurchID = @ChurchID,
--		GroupTypeID = @GroupTypeID,
--		PersonID = @PersonID

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

--END	

IF @Operation = 2
BEGIN
	DELETE ChurchesRegistersFamilies
	WHERE ChurchID = @ChurchID AND RegisterTypeID = @RegisterTypeID AND 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].[ChurchRegistersPersonsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersPersonsSet]
	@Operation				int = -1,
	@ChurchID				int,
	@RegisterTypeID			int,
	@PersonID				int,
	@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 ChurchID = @ChurchID AND RegisterTypeID = @RegisterTypeID AND PersonID = @PersonID)
BEGIN
	SET @ErrorCode = 2
	GOTO __END
END

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

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

--IF @Operation = 1
--BEGIN
--	UPDATE ChurchesGroupsPersons
--	SET
--		ChurchID = @ChurchID,
--		GroupTypeID = @GroupTypeID,
--		PersonID = @PersonID

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

--END	

IF @Operation = 2
BEGIN
	DELETE ChurchesRegistersPersons
	WHERE ChurchID = @ChurchID AND RegisterTypeID = @RegisterTypeID AND PersonID = @PersonID

	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].[FamiliesRegistersTypesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[DistrictsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[MaxPersonsRegisterNoGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MaxPersonsRegisterNoGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	

	SELECT  MAX(RegisterNo)
	FROM ChurchesRegistersPersons
	WHERE ((ChurchID IS NULL AND @ChurchID IS NULL) OR ChurchID = @ChurchID)
		AND (RegisterTypeID = @RegisterTypeID)
GO
/****** Object:  StoredProcedure [dbo].[MaxFamiliesRegisterNoGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MaxFamiliesRegisterNoGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	

	SELECT  MAX(RegisterNo)
	FROM ChurchesRegistersFamilies
	WHERE ((ChurchID IS NULL AND @ChurchID IS NULL) OR ChurchID = @ChurchID)
		AND (RegisterTypeID = @RegisterTypeID)
GO
/****** Object:  StoredProcedure [dbo].[RegistersTypesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[TransferredPersonsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransferredPersonsSet]
	@Operation				int = -1,
	@PersonID				int = -1,
	@ChurchID				int,
	@ChurchName				nvarchar(50)
	
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0

-- check parameters

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


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

	WHERE
		PersonID = @PersonID

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

END	


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

RETURN
GO
/****** Object:  StoredProcedure [dbo].[MotherByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[PersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonGet]
	@PersonID INT = -1 
AS

	/* SET NOCOUNT ON */

SELECT
		PersonID,
		AddressID,
		LastName,
		FirstName,
		MaidenName,
		FatherID,
		MotherID,
		BirthDate,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		Photo,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName
FROM Persons P
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[ChurchPersonsBySexGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

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

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
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, GETDATE()) 
				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
FROM Persons P
WHERE ((P.ChurchID IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID) 
	AND (P.Sex = @Sex OR @Sex = 2)
GO
/****** Object:  StoredProcedure [dbo].[ChurchPersonsAllGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchPersonsAllGet]
	@ChurchID	int = -1
AS

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P
WHERE ((P.ChurchID IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChildrenSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChildrenSet]
	@Operation				int = -1,
	@PersonID				int = -1 OUTPUT,
	@AddressID				int,
	@LastName				nvarchar(50),
	@FirstName				nvarchar(50),
	@MaidenName				nvarchar(50),
	@FatherID				int,
	@MotherID				int,
	@BirthDate				datetime,
	@PlaceOfBirth			nvarchar(50),
	@CNP					nvarchar(50),
	@Series					nvarchar(50),
	@SeriesNumber			nvarchar(50),
	@Sex					bit,
	@Photo					varbinary(MAX),
	@HomePhone				nvarchar(50),
	@MobilePhone			nvarchar(50),
	@DeathDate				datetime,
	@Details				nvarchar(256),
	@ChurchID				int,
	@ChurchName				nvarchar(50)
	
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)
	VALUES(	@AddressID,
			@LastName,
			@FirstName,
			@MaidenName,
			@FatherID,
			@MotherID,
			@BirthDate,
			@PlaceOfBirth,
			@CNP,
			@Series,
			@SeriesNumber,
			@Sex,
			@Photo,
			@HomePhone,
			@MobilePhone,
			@DeathDate,
			@Details,
			@ChurchID,
			@ChurchName)

	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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChildrenByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */

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,
		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
FROM Persons P
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
WHERE (FatherID = @PersonID OR MotherID = @PersonID)
GO
/****** Object:  StoredProcedure [dbo].[FatherByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[AddressByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[BaptismPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptismPersonsGet]
	@BaptismChurchID int = -1,
	@BaptismDate datetime
AS

	/* SET NOCOUNT ON */

    SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		(case when P.ChurchID IS NULL OR P.ChurchID <> @BaptismChurchID THEN cast(0 as bit) else cast(1 as bit) END) 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName
	FROM Persons P
		INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
	WHERE ((@BaptismChurchID IS NULL AND B.BaptismChurchID IS NULL) OR B.BaptismChurchID = @BaptismChurchID)
		AND ((B.BaptismDate IS NULL AND @BaptismDate IS NULL) OR B.BaptismDate = @BaptismDate)
GO
/****** Object:  StoredProcedure [dbo].[BaptismByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptismByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT * FROM Baptisms
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[BaptizedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptizedPersonsGet]
AS

	/* 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,
		AddressID,
		LastName,
		FirstName,
		MaidenName,
		FatherID,
		MotherID,
		BirthDate,
		(CASE WHEN BirthDate IS NOT NULL THEN 
			(CASE WHEN DeathDate IS NULL THEN dbo.fn_CalculateAge(BirthDate, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		P.ChurchID,
		--ChurchName
		(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName
	FROM Persons P
		INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[BaptismsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BaptismsGet]
	@ChurchID	int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
SELECT
	B.BaptismID,
	PersonID,
	BaptismChurchID,
	--BaptismChurch,
	(case when BaptismChurchID IS NULL THEN BaptismChurch else @churchName 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 ((@ChurchID IS NULL AND BaptismChurchID IS NULL) OR BaptismChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[BlessingByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BlessingByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT * FROM Blessings
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[FamilyChildrenGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamilyChildrenGet]
	@MarriageID	int = -1
AS

	/* SET NOCOUNT ON */

SELECT 		
	(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
	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, GETDATE()) 
				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
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)
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].[BlessingsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[ChurchMembersGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchMembersGet]
	@ChurchID	int = -1
AS

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

SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		-- 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, GETDATE()) 
				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
FROM Persons P
	INNER JOIN Baptisms B ON (P.PersonID = B.PersonID)
WHERE ((P.ChurchID IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchGroupsPersonsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[ChurchesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesSet]
	@Operation		int = -1,
	@ChurchID		int = -1 OUTPUT,
	@Name			nvarchar(50),
	@Address		nvarchar(256),
	@IsChild		bit
AS

/* SET NOCOUNT ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

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

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

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

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

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

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

END	

IF @Operation = 2
BEGIN

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

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

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

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

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

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

	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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchesServantsSet]
	@Operation			int = -1,
	@ChurchServantID	int = -1 OUTPUT,
	@ChurchID			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(@ChurchID, @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 = @ChurchID,
		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].[ChurchRegistersFamiliesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersFamiliesGet]
	@ChurchID	int = -1,
	@RegisterTypeID int = -1
AS
	/* SET NOCOUNT ON */
	
	SELECT  CRF.RegisterNo,
		CRF.ChurchID,
		CRF.RegisterTypeID,
		CRF.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, GETDATE()) 
				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, GETDATE()) 
				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,
		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].[DeparturesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeparturesSet]
	@Operation			int = -1,
	@DepartureID		int = -1 OUTPUT,
	@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 (PersonID, IsActive, DepartureDate, ReturnDate, DepartureAddress, DepartureDetails)
	VALUES(@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
		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].[PersonPropertiesTypesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[HolySpiritBaptismByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritBaptismByPersonGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT * FROM HolySpiritBaptisms
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[GroupsTypesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[FamiliesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamiliesGet]
AS
	/* SET NOCOUNT ON */
	
	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, GETDATE()) 
				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, GETDATE()) 
				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,
		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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FamiliesByChurchGet]
	@ChurchID	int = -1
AS

	/* SET NOCOUNT ON */
	
	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, GETDATE()) 
				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, GETDATE()) 
				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,
		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)

	WHERE (PW.ChurchID = @ChurchID OR PH.ChurchID = @ChurchID)
	ORDER BY FamilyName
GO
/****** Object:  StoredProcedure [dbo].[ChurchBaptizedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchBaptizedPersonsGet]
	@ChurchID int = -1
AS

	DECLARE @churchName nvarchar(50)
	SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
	/* SET NOCOUNT ON */

    SELECT 
		B.BaptismID, 
		B.BaptismChurchID, 
		B.BaptismDate, 
		(case when B.BaptismChurchID IS NULL THEN BaptismChurch else C.Name END) AS BaptismChurch, 
		B.BaptismServant,  
		P.PersonID,
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		(case when T.TransferredFromChurchID IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS IsTransferred,
		AddressID,
		LastName,
		FirstName,
		MaidenName,
		FatherID,
		MotherID,
		BirthDate,
		(CASE WHEN BirthDate IS NOT NULL THEN 
			(CASE WHEN DeathDate IS NULL THEN dbo.fn_CalculateAge(BirthDate, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
	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 (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)

    WHERE (EXISTS(SELECT * FROM Transfers WHERE PersonID = P.PersonID) OR (P.ChurchID = @ChurchID))
GO
/****** Object:  UserDefinedFunction [dbo].[IsBaptized]    Script Date: 03/31/2010 21:30:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[MarriageByPersonGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MarriageByPersonGet]
	@PersonID	int = -1
AS

	/* SET NOCOUNT ON */

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, GETDATE()) 
				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,
		
		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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[MarriagesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MarriagesSet]
	@Operation				int = -1,
	@MarriageID				int = -1 OUTPUT,
	@Divorce				bit,
	@HusbandID				int,
	@WifeID					int,
	@CivilServiceDate		datetime,
	@ReligiousServiceDate	datetime,
	@WeddingChurchID		int,
	@WeddingChurch			nvarchar(50),
	@WeddingServants		nvarchar(256)
	
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].[HolySpiritBaptismsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[PropertiesTypesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[PersonFinancialPositionsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonFinancialPositionGet]
	@PersonID INT = -1
AS

	/* SET NOCOUNT ON */
SELECT * FROM PersonsFinancialPositions
WHERE PersonID = @PersonID
GO
/****** Object:  StoredProcedure [dbo].[PersonsSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsSet]
	@Operation				int = -1,
	@PersonID				int = -1 OUTPUT,
	@AddressID				int,
	@LastName				nvarchar(50),
	@FirstName				nvarchar(50),
	@MaidenName				nvarchar(50),
	@FatherID				int,
	@MotherID				int,
	@BirthDate				datetime,
	@PlaceOfBirth			nvarchar(50),
	@CNP					nvarchar(50),
	@Series					nvarchar(50),
	@SeriesNumber			nvarchar(50),
	@Sex					bit,
	@Photo					varbinary(MAX),
	@HomePhone				nvarchar(50),
	@MobilePhone			nvarchar(50),
	@DeathDate				datetime,
	@Details				nvarchar(256),
	@ChurchID				int,
	@ChurchName				nvarchar(50)
	
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)
	VALUES(	@AddressID,
			@LastName,
			@FirstName,
			@MaidenName,
			@FatherID,
			@MotherID,
			@BirthDate,
			@PlaceOfBirth,
			@CNP,
			@Series,
			@SeriesNumber,
			@Sex,
			@Photo,
			@HomePhone,
			@MobilePhone,
			@DeathDate,
			@Details,
			@ChurchID,
			@ChurchName)

	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

	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
		
		-- 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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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:  StoredProcedure [dbo].[UnBaptizedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UnBaptizedPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

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

	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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
		 
	FROM Persons P
	WHERE (NOT EXISTS (SELECT * FROM Baptisms B WHERE P.PersonID = B.PersonID)) 
		AND ((@ChurchID IS NULL AND P.ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[TransfersToGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersToGet]
	@ChurchID int = -1
AS
	/* SET NOCOUNT ON */

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

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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate,
	Details,
	P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName	

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)
WHERE ((T.TransferredToChurchID IS NULL AND @ChurchID IS NULL) OR (T.TransferredToChurchID = @ChurchID))
GO
/****** Object:  StoredProcedure [dbo].[TransfersSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersSet]
	@Operation					int = -1,
	@TransferID					int = -1 OUTPUT,
	@PersonID					int,	
	@TransferredFromChurchID	int,	
	@TransferredFromChurchName	nvarchar(50),	
	@TransferredToChurchID		int,	
	@TransferredToChurchName	nvarchar(50),	
	@TransferDate				datetime,
	@TransferAct				nvarchar(50),
	@TransferDetails			nvarchar(256)
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

IF @Operation = 0
BEGIN 
	INSERT Transfers (	PersonID, 
						TransferredFromChurchID, 
						TransferredFromChurchName, 
						TransferredToChurchID,
						TransferredToChurchName,
						TransferDate,
						TransferAct,
						TransferDetails)
	VALUES( @PersonID, 
			@TransferredFromChurchID, 
			@TransferredFromChurchName, 
			@TransferredToChurchID,
			@TransferredToChurchName,
			@TransferDate,
			@TransferAct,
			@TransferDetails)


	IF @@ERROR <> 0
	BEGIN
		SET @ErrorCode = 3
		GOTO __END
	END
	SET @TransferID = @@IDENTITY
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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersGet]
AS
	/* SET NOCOUNT ON */

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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate,  
	Details,
	P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else C.Name END) AS ChurchName

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)
ORDER BY T.TransferDate, P.LastName, P.FirstName
GO
/****** Object:  StoredProcedure [dbo].[TransfersFromGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TransfersFromGet]
	@ChurchID int = -1
AS
	/* SET NOCOUNT ON */
	
DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID

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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate,
	Details,
	P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName	

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)
WHERE ((T.TransferredFromChurchID IS NULL AND @ChurchID IS NULL) OR (T.TransferredFromChurchID = @ChurchID))
GO
/****** Object:  StoredProcedure [dbo].[ServantsTitlesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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].[ServantsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ServantsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */
DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID

SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		ST.ServantTitleID, 
		ST.Name ServantTitle,
		CS.ChurchServantID, 
		CS.ServantID, 
		CS.IsActive,
		CS.ServantBecomeDate,
		CS.ServantRetireDate,
		CS.ServantDetails,
		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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P
	INNER JOIN ChurchesServants CS ON CS.ServantID = P.PersonID
	INNER JOIN ServantsTitles ST ON CS.ServantTitleID = ST.ServantTitleID
WHERE ((CS.ChurchID IS NULL AND @ChurchID IS NULL) OR (CS.ChurchID = @ChurchID))
GO
/****** Object:  StoredProcedure [dbo].[TransferredFromChurchGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|0|0|C:\Users\Beni\AppData\Local\Temp\~vs6C6.sql
ALTER PROCEDURE [dbo].[TransferredFromChurchGet]
	@ChurchID int  = -1,
	@PersonID int = -1
AS

	/* SET NOCOUNT ON */

SELECT 
	--(case when T.TransferredFromChurchID IS NULL THEN cast(0 as bit) else cast(1 as bit) END) AS IsTransferred,
	P.PersonID
FROM Persons P
	INNER 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)
WHERE (P.PersonID = @PersonID)
GO
/****** Object:  StoredProcedure [dbo].[SpecialCasesSet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpecialCasesSet]
	@Operation				int = -1,
	@SpecialCaseID			int = -1 OUTPUT,
	@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 (PersonID, IsActive, SpecialCaseDate, SolvedDate, SpecialCaseDetails)
	VALUES(@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
		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: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpecialCasesPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

	DECLARE @churchName nvarchar(50)
	SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
    SELECT
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive, 
		dbo.IsBaptized(P.PersonID) IsMember,
		SC.SpecialCaseID,
		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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
		 
	FROM Persons P
		INNER JOIN SpecialCases SC ON (P.PersonID = SC.PersonID)
    WHERE (P.ChurchID = @ChurchID OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[PersonsJobsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsJobsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

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

SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		dbo.IsBaptized(P.PersonID) 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,  
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P 
	LEFT OUTER JOIN PersonsFinancialPositions PFP ON (P.PersonID = PFP.PersonID)
		LEFT OUTER JOIN Jobs J ON (PFP.JobID = J.JobID)
WHERE ((@ChurchID IS NULL AND P.ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[PersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\Beni\AppData\Local\Temp\~vsC4F5.sql
-- PartnerMale = 0,
-- PartnerFemale = 1,
-- Father = 2,
-- Mother = 3,
-- Child = 4

ALTER PROCEDURE [dbo].[PersonsGet]
AS

SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		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, GETDATE()) 
				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
FROM Persons P
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[PersonsByPhotoGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsByPhotoGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
		
SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		dbo.IsBaptized(PersonID) IsMember, 
		(case when Photo IS NULL THEN cast(0 as bit) else cast(1 as bit) END) HasPhoto,
		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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P  
WHERE ((@ChurchID IS NULL AND ChurchID IS NULL) OR ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[PersonsAddressesGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonsAddressesGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

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

SELECT
	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive, 
	dbo.IsBaptized(P.PersonID) 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P 
	LEFT OUTER JOIN Addresses A ON (P.AddressID = A.AddressID)
    LEFT OUTER JOIN Districts D ON (A.DistrictID = D.DistrictID)
WHERE (P.ChurchID = @ChurchID OR (@ChurchID IS NULL AND P.ChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[HolySpiritUnBaptizedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritUnBaptizedPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

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

SELECT 
	(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
	dbo.IsBaptized(P.PersonID) 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
	PlaceOfBirth,
	CNP,
	Series,
	SeriesNumber,
	Sex,
	HomePhone,
	MobilePhone,
	DeathDate,
	Details,
	P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P
WHERE (NOT EXISTS (SELECT * FROM HolySpiritBaptisms B WHERE P.PersonID = B.PersonID))
		AND ((@ChurchID IS NULL AND P.ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[HolySpiritBaptizedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolySpiritBaptizedPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

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

    SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		dbo.IsBaptized(P.PersonID) 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
	FROM Persons P
		INNER JOIN HolySpiritBaptisms HSB ON (P.PersonID = HSB.PersonID)
    WHERE ((@ChurchID IS NULL AND P.ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchRegistersPersonsGet]
	@ChurchID int = -1,
	@RegisterTypeID int = -1
AS

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

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].[DepartedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\Beni\AppData\Local\Temp\~vsC4F5.sql

ALTER PROCEDURE [dbo].[DepartedPersonsGet]
	@ChurchID	int = -1
AS

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

SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive,
		dbo.IsBaptized(P.PersonID) IsMember,
		D.DepartureID, 
		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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,
		Details,
		ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P
	INNER JOIN Departures D ON D.PersonID = P.PersonID
WHERE ((P.ChurchID IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchPersonsGet]
	@ChurchID	int = -1
AS

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
SELECT 
		(case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsMember,
		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, GETDATE()) 
				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
FROM Persons P
WHERE ((P.ChurchID IS NULL AND @ChurchID IS NULL) OR P.ChurchID = @ChurchID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchGroupsPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchGroupsPersonsGet]
	@ChurchID int  = -1,
	@GroupTypeID int = -1
AS

	/* SET NOCOUNT ON */

DECLARE @churchName nvarchar(50)
SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
SELECT (case when DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		(case when (dbo.IsBaptized(P.PersonID) = 1 AND P.ChurchID = @ChurchID) THEN cast(1 as bit) else cast(0 as bit) END) IsMember,
		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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate,  
		Details,
		-- P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
FROM Persons P
	INNER JOIN ChurchesGroupsPersons CGP ON CGP.PersonID = P.PersonID
	INNER JOIN GroupsTypes GT ON CGP.GroupTypeID = GT.GroupTypeID
WHERE CGP.ChurchID = @ChurchID AND CGP.GroupTypeID = @GroupTypeID
GO
/****** Object:  StoredProcedure [dbo].[BlessedPersonsGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BlessedPersonsGet]
	@ChurchID int = -1
AS

	/* SET NOCOUNT ON */

	DECLARE @churchName nvarchar(50)
	SELECT @churchName = C.Name FROM Churches C WHERE C.ChurchID = @ChurchID
	
    SELECT
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) AS IsAlive, 
		dbo.IsBaptized(P.PersonID) IsMember,
		B.BlessingID, 
		B.BlessingChurchID, 
		B.BlessingDate, 
		(case when B.BlessingChurchID IS NULL THEN BlessingChurch else C.Name 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, GETDATE()) 
				ELSE dbo.fn_CalculateAge(BirthDate, DeathDate) END) END) Ages,
		PlaceOfBirth,
		CNP,
		Series,
		SeriesNumber,
		Sex,
		HomePhone,
		MobilePhone,
		DeathDate, 
		Details,
		P.ChurchID,
		(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName
	FROM Persons P
		INNER JOIN Blessings B ON (P.PersonID = B.PersonID)
		LEFT OUTER JOIN Churches C ON (B.BlessingChurchID = C.ChurchID)
	WHERE (B.BlessingChurchID = @ChurchID OR (@ChurchID IS NULL AND B.BlessingChurchID IS NULL))
GO
/****** Object:  StoredProcedure [dbo].[ChurchChildrenGet]    Script Date: 03/31/2010 21:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChurchChildrenGet]
	@ChurchID	int = -1
AS

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

SELECT 
		(case when P.DeathDate IS NULL THEN cast(1 as bit) else cast(0 as bit) END) IsAlive,
		dbo.IsBaptized(P.PersonID) IsBaptized,
		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, GETDATE()) 
				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 @churchName END) AS ChurchName,
		(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 (PM.PersonID IS NOT NULL AND PM.DeathDate IS NULL) THEN cast(1 as bit) else cast(0 as bit) 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,
		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)
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
