/***********************************************************************************

			CE CODE FONCTIONNE AVEC LA CTP2 (juillet 2007) DE SQL SERVER 2008
			Il accompagne un article technique sur le type HierarchyID :
				http://blog.djeepy1.net
				http://www.bewise.fr

***********************************************************************************/
Use AdventureWorks
GO


--méthode avec CTE pour hiérarchiser une table 
WITH UpperHierarchy(EmployeeId, LastName, Manager, HierarchyOrder)
AS
(
    SELECT emp.EmployeeId, emp.LoginId, emp.LoginId, 1 AS HierarchyOrder
    FROM HumanResources.Employee AS emp
	WHERE emp.ManagerId is Null
    UNION ALL
    SELECT emp.EmployeeId, emp.LoginId, Parent.LastName, HierarchyOrder + 1
    FROM HumanResources.Employee AS emp
		INNER JOIN UpperHierarchy AS Parent
			ON emp.ManagerId = parent.EmployeeId
)
SELECT *
From UpperHierarchy



--############################
--
-- UTILISATION DU NOUVEAU TYPE
--

--Création de la table de base
CREATE TABLE Organization
(
	EmployeeID hierarchyid NOT NULL,
	EmployeeName nvarchar(50) NOT NULL
)

--Ajout du niveau (champ calculé)
Alter Table dbo.Organization 
Add HierarchyLevel As EmployeeID.GetLevel() 

--indexation breadth-first (par largeur)
CREATE INDEX IX_ParLargeur 
ON Organization(HierarchyLevel,EmployeeID) ;

--ajout de l'unicité des noeuds
ALTER TABLE dbo.Organization ADD CONSTRAINT
	PK_Organization PRIMARY KEY
	(
	EmployeeID
	)

--ajout de la contrainte du parent (champ calculé + FK)
Alter Table dbo.Organization
Add ParentId AS EmployeeId.GetAncestor(1) PERSISTED
	REFERENCES dbo.Organization(EmployeeId)


--remplissage (sans unicité, les siblings ont la même valeur) ----> GENERE UNE ERREUR
WITH UpperHierarchy(EmpId, LastName, Node)
AS
(
    SELECT EmployeeId, LoginId, hierarchyid::GetRoot()
    FROM HumanResources.Employee
	WHERE ManagerId is Null
    UNION ALL
    SELECT Sub.EmployeeId, Sub.LoginId, Parent.Node.GetDescendant(null, null)
    FROM HumanResources.Employee AS Sub
		INNER JOIN UpperHierarchy AS Parent
			ON Sub.ManagerId = Parent.EmpId
)
Insert Into dbo.Organization(EmployeeId, EmployeeName)
Select Node, LastName 
From UpperHierarchy

--remplissage (avec unicité)
WITH UpperHierarchy(EmpId, LastName, Node)
AS
(
    SELECT EmployeeId, LoginId, hierarchyid::GetRoot()
    FROM HumanResources.Employee
	WHERE ManagerId is Null
    UNION ALL
    SELECT Sub.EmployeeId, Sub.LoginId, hierarchyId::Parse(Parent.Node.ToString() + Cast(ContactId as varchar(5)) + '/')--contactID est unique par noeud sibling mais ce n'est pas une valeur pertinente
    FROM HumanResources.Employee AS Sub
		INNER JOIN UpperHierarchy AS Parent
			ON Sub.ManagerId = Parent.EmpId
)
Insert Into dbo.Organization(EmployeeId, EmployeeName)
Select Node, LastName 
From UpperHierarchy




--####################################################################################"----
--
-- TESTS DE PERFS
--

--Avec CTE
WITH UpperHierarchy(EmployeeId, LastName, Manager, HierarchyOrder)
AS
(
    SELECT emp.EmployeeId, emp.LoginId, emp.LoginId, 1 AS HierarchyOrder
    FROM HumanResources.Employee AS emp
	WHERE emp.LoginId = 'adventure-works\james1'
    UNION ALL
    SELECT emp.EmployeeId, emp.LoginId, Parent.LastName, HierarchyOrder + 1
    FROM HumanResources.Employee AS emp
		INNER JOIN UpperHierarchy AS Parent
			ON emp.ManagerId = parent.EmployeeId
)
SELECT EmployeeId, LastName
From UpperHierarchy
GO
--Avec HierarchyID
Declare @BossNode As HierarchyId
Select @BossNode = EmployeeID From dbo.Organization Where EmployeeName = 'adventure-works\james1'

Select *
From dbo.Organization 
Where @BossNode.IsDescendant(EmployeeId) = 1