CREATE PROCEDURE dbo.aip_postldaporgtree
(
/*    Creates OrgTree From ldap

    2005-06-08    WMM MDB 024 collation.
    2005-03-08    WMM add index which is part of MDB definition.
    2004-08-30    Bill Merrow, Computer Associates
*/

--    Message severity set on error or completion
--    0 - Sucessful
--    1 - Warning
--    2 - Trapped error
--    3 - Runtime error
  


--     Message ID for debugging purposes
    @intMsgID int = 0 OUTPUT,

--    Message source for debugging purposes
    @strMsgSrc nvarchar(32) = 'aip_postldaporgtree' OUTPUT


)
AS
--    Set environmewnt
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

--     Enable NOCOUNT option to suppress unnecessary messages
    SET NOCOUNT ON


DECLARE @PMF nvarchar(32)
DECLARE @LastPMF nvarchar(32)
DECLARE @reportsTo nvarchar(128)
DECLARE @LastMgr nvarchar(32)
DECLARE @LastMgr1 nvarchar(32)
DECLARE @LastMgr2 nvarchar(32)
DECLARE @LastMgr3 nvarchar(32)
DECLARE @LastMgr4 nvarchar(32)
DECLARE @tmp nvarchar(128)
DECLARE @nvtmp nvarchar(128)
DECLARE @nvPMF nvarchar(32)
DECLARE @countids int
DECLARE @LoopCount int
DECLARE @RowCount int

if Object_ID('tempdb..#LDAPTemp') is not null drop table #LDAPTemp

    CREATE TABLE [#LDAPTemp] (
        [pmf] [nvarchar] (32) NOT NULL ,
        [reportsto] [nvarchar] (256) NOT NULL
    ) ON [PRIMARY]

DECLARE Main CURSOR for
SELECT distinct samid as PMF,mgr as ReportsTo from ai_usrdef_prop
ORDER by PMF
OPEN Main

--SELECT  @nvPMF as N'pmf',@nvtmp as N'reportsto' into #LDAPTemp

-- Whatever the source, all that is needed is the object name (PMF) and the parent (ReportsTo)

FETCH Main into @PMF,@reportsTo
WHILE (@@fetch_status <> -1)
BEGIN
    SELECT @tmp = '/' + @reportsTo
    SELECT @LastMgr = ''
    SELECT @LastMgr1 = ''
    SELECT @LastMgr2 = ''
    SELECT @LastMgr3 = ''
    SELECT @LastMgr4 = ''
    SELECT @LoopCount = 0
    -- INSERT #LDAPTemp
    -- SELECT @PMF,@tmp
    -- While loop checks are to detect loops in manager - we check current and 5 previous.
    while (@reportsTo is not NULL and @reportsTo != @LastMgr and
        @reportsTo != @LastMgr1 and
        @reportsTo != @LastMgr2 and
        @reportsTo != @LastMgr3 and
        @reportsTo != @LastMgr4 and
        @LoopCount < 20 and
        @PMF != @reportsTo)
    -- Top Level Terminator
    Begin
        SELECT @LoopCount = @LoopCount + 1
        SELECT @LastMgr4 = @LastMgr3
        SELECT @LastMgr3 = @LastMgr2
        SELECT @LastMgr2 = @LastMgr1
        SELECT @LastMgr1 = @LastMgr1
        SELECT @LastMgr = @reportsTo
        SELECT @reportsTo = mgr from ai_usrdef_prop where samid = @reportsTo
        if @reportsTo != @LastMgr set @tmp = '/' + @reportsTo + @tmp
    End
    SELECT @nvPMF = @PMF
    SELECT @nvtmp = @tmp
    INSERT #LDAPTemp
    SELECT @nvPMF,@nvtmp
    -- UPDATE #LDAPTemp set reportsto = @tmp where pmf = @PMF
    FETCH Main into @PMF,@reportsTo
END

CLOSE Main
DEALLOCATE Main
DELETE from #LDAPTemp where pmf is null
set @RowCount = (SELECT count(*) from #LDAPTemp)

--    delete any rows in ai_usrdef_orgtree_prop
    DELETE FROM ai_usrdef_orgtree_prop
--    now copy all data just built in GTT into the real table
    INSERT into ai_usrdef_orgtree_prop select * from #LDAPTemp  --  #LDAPTemp contains the finished orgtree...

drop table #LDAPTemp

if @RowCount > 0 return(0)
else return(1)
GO
GRANT EXECUTE ON  [dbo].[aip_postldaporgtree] TO [aiadmin]
GO