-- $Id:
-- $Revision: 1.9 $
-- $Date: 2004/07/08 10:10:18 $

-- shared functions between applications
-- please use the standard documentation format below

/*
=============================================
Author : XXX
Created: 01/2001
Updated: 02/2001
=============================================

The documentation

=============================================
*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_Concatenation]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_Concatenation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_Intersection]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_Intersection]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_Negation]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_Negation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeAuthorId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeAuthorId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeDistributionId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeDistributionId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeFactId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeFactId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeNameId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeNameId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeRefDetId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeRefDetId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeRefId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeRefId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_NextFreeRelNameId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_NextFreeRelNameId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_Reverse]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_Reverse]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_Union]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_Union]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_add2Notes]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_add2Notes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_removeTrailingZeros]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_removeTrailingZeros]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_replNullBool]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_replNullBool]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_replNullHyphen]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_replNullHyphen]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_replNullSpace]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_replNullSpace]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_share_strTrim]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_share_strTrim]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*

=============================================
Author : M. Geoffroy
Created: 9/2003	
Updated: 9/2003	
=============================================

This function computes the relationship resulting 
between PT1 and PT3 from the given combined relationship 
between PT1 and PT2 and the given combined relationship between PT2 and PT3

=============================================

*/


CREATE     FUNCTION  [dbo].f_share_Concatenation (@QualifierFk1 int, @QualifierFk2 int) Returns int
AS
BEGIN
	DECLARE @RelQualifierFk AS int
	DECLARE @is_congruent1 AS bit
	DECLARE @is_included_in1 AS bit
	DECLARE @includes1 AS bit
	DECLARE @overlaps1 AS bit
	DECLARE @excludes1 AS bit
	DECLARE @doubtful1 AS bit

	DECLARE @is_congruent2 AS bit
	DECLARE @is_included_in2 AS bit
	DECLARE @includes2 AS bit
	DECLARE @overlaps2 AS bit
	DECLARE @excludes2 AS bit
	DECLARE @doubtful2 AS bit

	DECLARE @is_congruent AS bit
	DECLARE @is_included_in AS bit
	DECLARE @includes AS bit
	DECLARE @overlaps AS bit
	DECLARE @excludes AS bit
	DECLARE @doubtful AS bit

	DECLARE @is_congruentTemp AS bit
	DECLARE @is_included_inTemp AS bit
	DECLARE @includesTemp AS bit
	DECLARE @overlapsTemp AS bit
	DECLARE @excludesTemp AS bit

	SET @is_congruent1 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @is_included_in1 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @includes1 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @overlaps1 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @excludes1 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @doubtful1 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)

	SET @is_congruent2 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @is_included_in2 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @includes2 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @overlaps2 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @excludes2 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @doubtful2 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)

	SET @is_congruent = 0
	SET @is_included_in = 0
	SET @includes = 0
	SET @overlaps = 0
	SET @excludes = 0
	SET @doubtful = (@doubtful1) | (@doubtful2)


	IF (@is_congruent1) = 1 BEGIN
		SET @is_congruent = (@is_congruent2)
		SET @is_included_in = (@is_included_in2)
		SET @includes = (@includes2)
		SET @overlaps = (@overlaps2)
		SET @excludes = (@excludes2)
	END
	IF (@is_congruent2) = 1 BEGIN
		SET @is_congruent = (@is_congruent) | (@is_congruent1)
		SET @is_included_in = (@is_included_in) | (@is_included_in1)
		SET @includes = (@includes) | (@includes1)
		SET @overlaps = (@overlaps) | (@overlaps1)
		SET @excludes = (@excludes) | (@excludes1)
	END

	IF (@is_included_in1) = 1 BEGIN
		IF (@is_included_in2) = 1 BEGIN
			SET @is_included_in = 1
		END
		IF (@includes2) = 1 BEGIN
			SET @is_congruent = 1
			SET @is_included_in = 1
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
			GOTO FINISH
		END
		IF (@overlaps2) = 1 BEGIN
			SET @is_included_in = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
		IF (@excludes2) = 1 BEGIN
			SET @excludes = 1
		END
	END

	IF (@includes1) = 1 BEGIN
		IF (@is_included_in2) = 1 BEGIN
			SET @is_congruent = 1
			SET @is_included_in = 1
			SET @includes = 1
			SET @overlaps = 1
		END
		IF (@includes2) = 1 BEGIN
			SET @includes = 1
		END
		IF (@overlaps2) = 1 BEGIN
			SET @includes = 1
			SET @overlaps = 1
		END
		IF (@excludes2) = 1 BEGIN
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
	END

	IF (@overlaps1) = 1 BEGIN
		IF (@is_included_in2) = 1 BEGIN
			SET @is_included_in = 1
			SET @overlaps = 1

		END
		IF (@includes2) = 1 BEGIN
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
		IF (@overlaps2) = 1 BEGIN
			SET @is_congruent = 1
			SET @is_included_in = 1
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
			GOTO FINISH
		END
		IF (@excludes2) = 1 BEGIN
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
	END

	IF (@excludes1) = 1 BEGIN
		IF (@is_included_in2) = 1 BEGIN
			SET @is_included_in = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
		IF (@includes2) = 1 BEGIN
			SET @excludes = 1
		END
		IF (@overlaps2) = 1 BEGIN
			SET @is_included_in = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
		IF (@excludes2) = 1 BEGIN
			SET @is_congruent = 1
			SET @is_included_in = 1
			SET @includes = 1
			SET @overlaps = 1
			SET @excludes = 1
		END
	END


	FINISH:
	SET @RelQualifierFk = (SELECT RelPTQualifierId FROM RelPtQualifier
							WHERE is_congruent = @is_congruent AND
								is_included_in = @is_included_in AND
								includes = @includes AND
								overlaps = @overlaps AND
								excludes = @excludes AND
								doubtful = @doubtful AND
								is_concept_relation = 1)

	RETURN @RelQualifierFk
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*

=============================================
Author : M. Geoffroy
Created: 9/2003	
Updated: 9/2003	
=============================================

This function computes the relationship resulting 
from the intersection of two combined relationships

=============================================

*/


CREATE  FUNCTION  [dbo].f_share_Intersection (@QualifierFk1 int, @QualifierFk2 int) Returns int
AS
BEGIN
	DECLARE @RelQualifierFk AS int
	DECLARE @is_congruent1 AS bit
	DECLARE @is_included_in1 AS bit
	DECLARE @includes1 AS bit
	DECLARE @overlaps1 AS bit
	DECLARE @excludes1 AS bit
	DECLARE @doubtful1 AS bit

	DECLARE @is_congruent2 AS bit
	DECLARE @is_included_in2 AS bit
	DECLARE @includes2 AS bit
	DECLARE @overlaps2 AS bit
	DECLARE @excludes2 AS bit
	DECLARE @doubtful2 AS bit

	DECLARE @is_congruent AS bit
	DECLARE @is_included_in AS bit
	DECLARE @includes AS bit
	DECLARE @overlaps AS bit
	DECLARE @excludes AS bit
	DECLARE @doubtful AS bit

	SET @is_congruent1 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @is_included_in1 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @includes1 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @overlaps1 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @excludes1 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @doubtful1 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)

	SET @is_congruent2 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @is_included_in2 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @includes2 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @overlaps2 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @excludes2 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @doubtful2 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)

	IF @doubtful1 = @doubtful2 BEGIN
		SET @is_congruent = (@is_congruent1) & (@is_congruent2)
		SET @is_included_in = (@is_included_in1) & (@is_included_in2)
		SET @includes = (@includes1) & (@includes2)
		SET @overlaps = (@overlaps1) & (@overlaps2)
		SET @excludes = (@excludes1) & (@excludes2)
		SET @doubtful = (@doubtful1)
	END ELSE IF @doubtful1 = 0 BEGIN
		SET @is_congruent = (@is_congruent1)
		SET @is_included_in = (@is_included_in1)
		SET @includes = (@includes1)
		SET @overlaps = (@overlaps1)
		SET @excludes = (@excludes1)
		SET @doubtful = (@doubtful1)
	END ELSE BEGIN
		SET @is_congruent = (@is_congruent2)
		SET @is_included_in = (@is_included_in2)
		SET @includes = (@includes2)
		SET @overlaps = (@overlaps2)
		SET @excludes = (@excludes2)
		SET @doubtful = (@doubtful2)
	END

	SET @RelQualifierFk = (SELECT RelPTQualifierId FROM RelPtQualifier
							WHERE is_congruent = @is_congruent AND
								is_included_in = @is_included_in AND
								includes = @includes AND
								overlaps = @overlaps AND
								excludes = @excludes AND
								doubtful = @doubtful AND
								is_concept_relation = 1)

	RETURN @RelQualifierFk
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*

=============================================
Author : M. Geoffroy
Created: 9/2003	
Updated: 9/2003	
=============================================

This function computes the (non) relationship 
between PT1 and PT2 on the basis of a given relationship

=============================================

*/


CREATE     FUNCTION  [dbo].f_share_Negation (@QualifierFk int) Returns int
AS
BEGIN
	DECLARE @RelQualifierFk AS int
	DECLARE @is_congruent AS bit
	DECLARE @is_included_in AS bit
	DECLARE @includes AS bit
	DECLARE @overlaps AS bit
	DECLARE @excludes AS bit
	DECLARE @doubtful AS bit

	SET @is_congruent = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @is_included_in = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @includes = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @overlaps = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @excludes = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @doubtful = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)

	SET @RelQualifierFk = (SELECT RelPTQualifierId FROM RelPtQualifier
							WHERE is_congruent = ~@is_congruent AND
								is_included_in = ~@is_included_in AND
								includes = ~@includes AND
								overlaps = ~@overlaps AND
								excludes = ~@excludes AND
								doubtful = @doubtful AND
								is_concept_relation = 1)

	RETURN @RelQualifierFk
END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO







/*
=============================================
Author: Karl GlÃ¼ck
Created: 11/2002
Updated: 11/2002
=============================================

Get  the minimum "unoccupied" AuthorId
=============================================
*/

CREATE  FUNCTION dbo.f_share_NextFreeAuthorId()  RETURNS INT
BEGIN
DECLARE @AuthorId  AS INTEGER

	SELECT @AuthorId = MAX (AuthorId) FROM Author
	SET @AuthorId = @AuthorId +1

	IF @AuthorId is NULL
	begin
		SET @AuthorId = 100
	end

  RETURN(@AuthorId)
END








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO







/*

=============================================
Author : Markus DÃ¶ring
Created: 11/2001
Updated: 07/2002
=============================================

Get  the minimum "unoccupied" DistributionId
greater than @Low

=============================================

*/

CREATE FUNCTION dbo.f_share_NextFreeDistributionId(@Low INT)  RETURNS INT
BEGIN
  DECLARE @i  AS INTEGER
  SET @i = @Low
  WHILE EXISTS(SELECT DistributionId FROM Distribution WHERE DistributionId = @i) BEGIN
              SET @i = @i +1
  END
  RETURN(@i)
END








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO






/*

=============================================
Author : Markus DÃ¶ring
Created: 11/2001
Updated: 07/2002
=============================================

Get  the minimum "unoccupied" FactId
greater than @Low

=============================================

*/

CREATE FUNCTION dbo.f_share_NextFreeFactId(@Low INT)  RETURNS INT
BEGIN
  DECLARE @i  AS INTEGER
  SET @i = @Low
  WHILE EXISTS(SELECT * FROM Fact WHERE FactId = @i) BEGIN
              SET @i = @i +1
  END
  RETURN(@i)
END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





/*
=============================================
Author : Karl GlÃ¼ck
Created: 11/2002
Updated: 
=============================================

Get  the nextfree NameId if new table set to 100

=============================================
*/

CREATE FUNCTION dbo.f_share_NextFreeNameId()  RETURNS INT
BEGIN
  DECLARE @NameId  AS INTEGER

	SELECT @NameId = MAX (NameId) FROM Name
	SET @NameId = @NameId +1

	IF @NameId is NULL
	begin
		SET @NameId = 100
	end

  RETURN(@NameId)
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO






/*

=============================================
Author : Markus DÃ¶ring
Created: 10/2002
Updated: 10/2002
=============================================

Get  the maximum RefDetailId plus 1

=============================================

*/

CREATE FUNCTION dbo.f_share_NextFreeRefDetId()  RETURNS INT
BEGIN
  DECLARE @RefId  AS INTEGER

	SELECT @RefId = MAX (RefDetailId) FROM RefDetail
	SET @RefId = @RefId +1

	IF @RefId is NULL
	begin
		SET @RefId = 100
	end

  RETURN(@RefId)
END









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO







/*

=============================================
Author : Markus DÃ¶ring
Created: 10/2002
Updated: 10/2002
=============================================

Get the maximum RefID plus 1

=============================================

*/

CREATE  FUNCTION dbo.f_share_NextFreeRefId(@Low INT)  RETURNS INT
BEGIN
  DECLARE @i  AS INTEGER
  SET @i = @Low
  WHILE EXISTS(SELECT * FROM Reference WHERE RefId = @i) BEGIN
              SET @i = @i +1
  END
  RETURN(@i)
END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/*

=============================================
Author : M. Geoffroy
Created: 04/2004
Updated: 04/2004
=============================================

Get  the minimum "unoccupied" RelNameId 
greater than @Low.

=============================================
*/

CREATE FUNCTION dbo.f_share_NextFreeRelNameId(@Low INT)  RETURNS INT
BEGIN
  DECLARE @i  AS INTEGER
  SET @i = @Low
  WHILE EXISTS(SELECT * FROM RelName WHERE RelNameId = @i) BEGIN
              SET @i = @i +1
  END
  RETURN(@i)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE     FUNCTION  [dbo].f_share_Reverse (@QualifierFk int) Returns int
AS
BEGIN
	DECLARE @RelQualifierFk AS int
	DECLARE @is_congruent AS bit
	DECLARE @is_included_in AS bit
	DECLARE @includes AS bit
	DECLARE @overlaps AS bit
	DECLARE @excludes AS bit
	DECLARE @doubtful AS bit

	SET @is_congruent = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @is_included_in = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @includes = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @overlaps = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @excludes = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)
	SET @doubtful = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk)

	SET @RelQualifierFk = (SELECT RelPTQualifierId FROM RelPtQualifier
							WHERE is_congruent = @is_congruent AND
								is_included_in = @includes AND
								includes = @is_included_in AND
								overlaps = @overlaps AND
								excludes = @excludes AND
								doubtful = @doubtful AND
								is_concept_relation = 1)

	RETURN @RelQualifierFk
END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*

=============================================
Author : M. Geoffroy
Created: 9/2003
Updated: 9/2003
=============================================

This function computes the relationship resulting 
from the union of two combined relationships
=============================================

*/

CREATE     FUNCTION  [dbo].f_share_Union (@QualifierFk1 int, @QualifierFk2 int) Returns int
AS
BEGIN
	DECLARE @RelQualifierFk AS int
	DECLARE @is_congruent1 AS bit
	DECLARE @is_included_in1 AS bit
	DECLARE @includes1 AS bit
	DECLARE @overlaps1 AS bit
	DECLARE @excludes1 AS bit
	DECLARE @doubtful1 AS bit

	DECLARE @is_congruent2 AS bit
	DECLARE @is_included_in2 AS bit
	DECLARE @includes2 AS bit
	DECLARE @overlaps2 AS bit
	DECLARE @excludes2 AS bit
	DECLARE @doubtful2 AS bit

	DECLARE @is_congruent AS bit
	DECLARE @is_included_in AS bit
	DECLARE @includes AS bit
	DECLARE @overlaps AS bit
	DECLARE @excludes AS bit
	DECLARE @doubtful AS bit

	SET @is_congruent1 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @is_included_in1 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @includes1 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @overlaps1 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @excludes1 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)
	SET @doubtful1 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk1)

	SET @is_congruent2 = (SELECT is_congruent FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @is_included_in2 = (SELECT is_included_in FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @includes2 = (SELECT includes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @overlaps2 = (SELECT overlaps FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @excludes2 = (SELECT excludes FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)
	SET @doubtful2 = (SELECT doubtful FROM RelPtQualifier WHERE RelPTQualifierId = @QualifierFk2)

	SET @is_congruent = (@is_congruent1) | (@is_congruent2)
	SET @is_included_in = (@is_included_in1) | (@is_included_in2)
	SET @includes = (@includes1) | (@includes2)
	SET @overlaps = (@overlaps1) | (@overlaps2)
	SET @excludes = (@excludes1) | (@excludes2)
	SET @doubtful = (@doubtful1) | (@doubtful2)

	SET @RelQualifierFk = (SELECT RelPTQualifierId FROM RelPtQualifier
							WHERE is_congruent = @is_congruent AND
								is_included_in = @is_included_in AND
								includes = @includes AND
								overlaps = @overlaps AND
								excludes = @excludes AND
								doubtful = @doubtful AND
								is_concept_relation = 1)

	RETURN @RelQualifierFk
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*

=============================================
Author : M.DÃ¶ring
Created: 2/2003
Updated: 2/2003
=============================================

append text to note and insert carriage return.

=============================================

*/

CREATE    FUNCTION dbo.f_share_add2Notes(@orig NVARCHAR(4000), @new NVARCHAR(4000))  RETURNS NVARCHAR(4000)
BEGIN
	if len(@new) > 0
		set @new = char(13) + @new
	else
		set @new = ''
  RETURN ( isnull(@orig,'') +@new )
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*

=============================================
Author : Markus DÃ¶ring
Created: 02/2003
Updated: 02/2003
=============================================

Removes trailing zeros from a string.
If the string consists of zeros only, 0 is returned.

=============================================
*/


CREATE    FUNCTION dbo.f_share_removeTrailingZeros(@source_id NVARCHAR(250))  RETURNS NVARCHAR(250)
BEGIN
	set @source_id = dbo.f_loc_replNullTrim(@source_id)

	-- cut off trailing zeros.
	while (left(@source_id,1)='0') begin
		set @source_id = right(@source_id, len(@source_id)-1)
	end
	
	if isnull(@source_id, '') = '' set @source_id = '0'

	RETURN(@source_id)
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



/*
=============================================
Author : Markus DÃ¶ring
Created: 09/2002
Updated: 
=============================================
function to replace existing boolean Nulls
with false.
=============================================
*/

CREATE FUNCTION dbo.f_share_replNullBool (@Variable bit)  RETURNS bit 
AS  
BEGIN 
if (@Variable is null) set @Variable=0 else set @Variable = @Variable  
return @Variable
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







/*
=============================================
Author : Markus DÃ¶ring
Created: 09/2002
Updated: 
=============================================
function to replace existing Nulls or zero length strings
with either "- " or add a blank.
=============================================
*/

CREATE     FUNCTION dbo.f_share_replNullHyphen (@Variable nvarchar(255))  RETURNS nvarchar(255) 
AS  
BEGIN 
if (@Variable is null or len(ltrim(rtrim(@Variable)))=0 ) set @Variable='- ' else set @Variable = @Variable +' '
return @Variable
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO







/*

=============================================
Author : Markus DÃ¶ring
Created: 01/2003
Updated: 01/2003
=============================================

Returns ' ' if the passed parameter is NULL or ''.
Otherwise returns original value.

=============================================
*/

create  FUNCTION dbo.f_share_replNullSpace (@Variable char)  RETURNS char 
AS  
BEGIN 
if (@Variable is null OR @Variable = '') set @Variable=' '
return @Variable
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/*
=============================================
Author : Markus DÃ¶ring
Created: 12/2002
Updated: 12/2002
=============================================

Converts an Integer to a string and trims
all whitespace from it, because SQL Server
somehow creates a lot of spaces around the number.

=============================================
*/

CREATE  FUNCTION dbo.f_share_strTrim (@x int) RETURNS NVARCHAR(4000)
AS
BEGIN
   	return rtrim(ltrim(str(@x)))
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

