USE [Church]
GO
/****** Object:  Table [dbo].[Grids]    Script Date: 06/25/2010 00:16:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Grids](
	[GridID] [int] NOT NULL,
	[AutoSizeColumnsMode] [int] NOT NULL,
 CONSTRAINT [PK_Grids] PRIMARY KEY CLUSTERED 
(
	[GridID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GridsColumns]    Script Date: 06/25/2010 00:16:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GridsColumns](
	[GridID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[HeaderText] [nvarchar](50) NOT NULL,
	[Visible] [bit] NOT NULL,
	[DisplayIndex] [int] NOT NULL,
	[Width] [int] NOT NULL,
 CONSTRAINT [PK_GridsColumns] PRIMARY KEY CLUSTERED 
(
	[GridID] ASC,
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[GridsSet]    Script Date: 06/25/2010 00:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsSet]
	@Operation				int = -1,
	@GridID					int,
	@AutoSizeColumnsMode	int
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

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

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

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

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

END	

IF @Operation = 2
BEGIN

	DELETE Grids
	WHERE
		GridID = @GridID

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

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

RETURN
GO
/****** Object:  StoredProcedure [dbo].[GridsGet]    Script Date: 06/25/2010 00:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsGet]
	@GridID	int = -1
AS

	/* SET NOCOUNT ON */

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

ORDER BY [GridID]
GO
/****** Object:  StoredProcedure [dbo].[GridsColumnsSet]    Script Date: 06/25/2010 00:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsColumnsSet]
	@Operation				int = -1,
	@GridID					int,
	@Name					nvarchar(50),
	@HeaderText				nvarchar(50),
	@Visible				bit,
	@DisplayIndex			int,
	@Width					int
AS

/* SET NOServantTitleID ON */

DECLARE @ErrorCode	int

SET @ErrorCode = 0


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

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

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

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

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

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

END	

IF @Operation = 2
BEGIN

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

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

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

RETURN
GO
/****** Object:  StoredProcedure [dbo].[GridsColumnsGet]    Script Date: 06/25/2010 00:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GridsColumnsGet]
	@GridID	int = -1
AS

	/* SET NOCOUNT ON */

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

ORDER BY DisplayIndex
GO
/****** Object:  StoredProcedure [dbo].[ChildrenByPersonGet]    Script Date: 06/25/2010 00:16:51 ******/
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,
		(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
	LEFT OUTER JOIN Churches C ON (P.ChurchID = C.ChurchID)
WHERE (FatherID = @PersonID OR MotherID = @PersonID)
GO
/****** Object:  StoredProcedure [dbo].[ChurchRegistersPersonsGet]    Script Date: 06/25/2010 00:16:51 ******/
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,
	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,
	-- P.ChurchID,
	(case when P.ChurchID IS NULL THEN P.ChurchName else @churchName END) AS ChurchName,
	A.Address,
	D.Name District
FROM ChurchesRegistersPersons AS CRP
	INNER JOIN Persons P ON (CRP.PersonID = P.PersonID)
		LEFT OUTER JOIN (SELECT T1.PersonID, T1.TransferDate, T1.TransferredFromChurchID FROM Transfers T1
			INNER JOIN (SELECT PersonID, MAX(TransferDate) TransferDate FROM Transfers 
				WHERE TransferredFromChurchID = @ChurchID OR TransferredToChurchID = @ChurchID GROUP BY PersonID) TL 
				ON (T1.PersonID = TL.PersonID AND T1.TransferDate = TL.TransferDate)) T 
				ON (P.PersonID = T.PersonID AND T.TransferredFromChurchID = @ChurchID)
	LEFT OUTER JOIN Addresses A ON P.AddressID = A.AddressID
		LEFT OUTER JOIN Districts D ON A.DistrictID = D.DistrictID
		
WHERE CRP.ChurchID = @ChurchID AND CRP.RegisterTypeID = @RegisterTypeID
	--((EXISTS(SELECT * FROM Transfers WHERE PersonID = P.PersonID)  OR )
ORDER BY CRP.RegisterNo
GO
/****** Object:  ForeignKey [FK_GridsColumns_Grids]    Script Date: 06/25/2010 00:16:52 ******/
ALTER TABLE [dbo].[GridsColumns]  WITH CHECK ADD  CONSTRAINT [FK_GridsColumns_Grids] FOREIGN KEY([GridID])
REFERENCES [dbo].[Grids] ([GridID])
GO
ALTER TABLE [dbo].[GridsColumns] CHECK CONSTRAINT [FK_GridsColumns_Grids]
GO
