
PRINT 'EXECUTE 012a'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_mclBook_getRelated_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_mclBook_getRelated_2]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.p_mclBook_getRelated_2
	@PTNameId int,
	@PTRefId int
AS
SELECT 
	rt1.PTNameFk1,
	rt1.PTRefFk1, 

	rn.RelNameQualifierFk,
	rn.RelNameQualifier,

	rt1.RelQualifierFk,
	rt1.RelPTQualifier,
	
	rt1.StatusId1, 
	rt1.Status1,

	doubtfulFlag = CASE WHEN rt1.doubtfulFlag = 'd' THEN 1 ELSE 0 END,

	rt1.NomStatusId,
	rt1.NomStatus,
	rt1.NomStatusRefFk,

	rt1.Name1, 	 
	rt1.FullName1, 

	rt1.Ref1, 
	rt1.FullNomRef1,
	rt1.AuthorTeam1, 
	rt1.RankId1, 
	rt1.RankAbbrev1,
	BaseName = CASE 
       		WHEN rn.FullName1 IS NOT NULL THEN rn.FullName1                 
       		WHEN rt1.RelQualifierFk IN (2, 6, 102, 104) AND rn.FullName1 IS NULL THEN rt1.FullName1 
       		ELSE NULL END,

	BaseNameId = CASE 
               WHEN rn.FullName1 IS NOT NULL THEN rn.NameFk1                 
               WHEN rt1.RelQualifierFk IN (2, 6, 102, 104) AND rn.FullName1 IS NULL THEN rt1.PTNameFk1 
               ELSE NULL END,

	/* ------------- sorting the results  ------------- */ 
	
	/* 1) the basionym of the given PTaxon as topmost element */
	TheBasionym = CASE rt1.PTNameFk1 WHEN rn2.NameFk1 THEN 1 ELSE 0 END,

    /* 2) sort by RelQualifierFk with custom order:
            homotypic > heterotypic > invalid designation > misapplied */
	RelQualifierFk_reorder = CASE   
        	WHEN rt1.RelQualifierFk=7 THEN -99   /* is homotypic */
        	WHEN rt1.RelQualifierFk=101 THEN -98 /* is pro parte and homotypic synonym of */
        	WHEN rt1.RelQualifierFk=103 THEN -97 /* is partial and homotypic synonym of */
            WHEN rt1.RelQualifierFk=8 OR ISNULL(nsr.NomStatusFk,0) = 1 THEN 998 /* is invalid designation for */
        	WHEN rt1.RelQualifierFk=3 THEN 999   /* is misapplied */
        	WHEN rt1.RelQualifierFk=2 THEN 6     /* is synonym but is heterotypic by implication */
        	ELSE rt1.RelQualifierFk END,

	/* 3) sort by concatenation of NomRefYear and BasionymName 
     	      clusters the heterotypic by BasionymName  */
        BaseName_Order = CASE 
		WHEN rn.FullName1 IS NOT NULL THEN
			/* change: was: rn.NomRefYear1 + REPLICATE (' ', 41 - LEN(rt1.NomRefYear1))+ rn.FullName1
				this makes no sense if rn.NomRefYear1 and rt1.NomRefYear1 don't have the same length */
			/* alt: 
			rn.NomRefYear1 + REPLICATE (' ', 41 - LEN(rn.NomRefYear1))+ rn.FullName1  */
			/* neu: sh, 3.12.2008 */
			isnull(rn.NomRefYear1,'99999') + isnull(REPLICATE (' ', 41 - LEN(rn.NomRefYear1)), replicate(' ',36))+ rn.FullName1      

		WHEN rt1.RelQualifierFk IN (2, 6, 102, 104) AND rn.FullName1 IS NULL AND rt1.NomRefYear1 IS NOT NULL THEN
			rt1.NomRefYear1 + REPLICATE (' ', 41 - LEN(rt1.NomRefYear1))+ rt1.FullName1
		WHEN rt1.RelQualifierFk IN (2, 6, 102, 104) AND rn.FullName1 IS NULL AND rt1.NomRefYear1 IS NULL THEN
			'99999' + REPLICATE (' ', 36)+ rt1.FullName1
		ELSE NULL END 

		 -- DEBUG - ,rt1.RelPTQualifier , rt1.FullName2 ,rn.RelNameQualifier, rn.FullName1, rn.NomRefYear1, rt1.NomRefYear1
        , rt1.NomRefYear1
        
FROM 
        EDIT_RelPTaxonNameRef AS rt1 
        LEFT OUTER JOIN dbo.EDIT_NameRelRef as rn 
            ON rt1.PTNameFk1 =  rn.NameFk2 AND rn.RelNameQualifierFk IN (1,3,18) 
        LEFT OUTER JOIN dbo.EDIT_NameRelRef as rn2                            
            ON rt1.PTNameFk2 =  rn2.NameFk2 AND rn2.RelNameQualifierFk IN (1)
        LEFT OUTER JOIN dbo.NomStatusRel as nsr
            ON rt1.PTNameFk1 =  nsr.NameFk
    

WHERE 
	rt1.PTNameFk2 = @PTNameId AND 
	rt1.PTRefFk2 = @PTRefId /*7520340|7000000*/ AND  
	rt1.RelQualifierFk IN (Select * from EDIT_f_RelQualifierFk_List('synmis'))
 
ORDER BY 
	TheBasionym DESC, 
	RelQualifierFk_reorder, 
	BaseName_Order, 
		/* old: 
		rn.RelNameQualifier, 
		rt1.NomRefYear1 */

	/* sh, changed 12.11.2008  */
	rt1.NomRefYear1,
	rn.RelNameQualifier

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

