text: USE [profiles_ucsf]
  GO
  
  /****** Object:  StoredProcedure [Profile.Data].[Publication.Entity.UpdateEntityOnePerson]    Script Date: 11/17/2017 12:39:41 PM ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  
  
  
  
  ALTER PROCEDURE [Profile.Data].[Publication.Entity.UpdateEntityOnePerson]
  	@PersonID INT
  AS
  BEGIN
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  	SET NOCOUNT ON;
  
   
  	-- *******************************************************************
  	-- *******************************************************************
  	-- Update InformationResource entities
  	-- *******************************************************************
  	-- *******************************************************************
   
   
  	----------------------------------------------------------------------
  	-- Get a list of current publications
  	----------------------------------------------------------------------
   
  	CREATE TABLE #Publications
  	(
  		PMID INT NULL ,
  		MPID NVARCHAR(50) NULL ,
  		PMCID NVARCHAR(55) NULL,
  		EntityDate DATETIME NULL ,
  		Reference VARCHAR(MAX) NULL ,
  		Source VARCHAR(25) NULL ,
  		URL VARCHAR(1000) NULL ,
  		Title VARCHAR(4000) NULL ,
  		Authors VARCHAR(4000) NULL
  	)
   
  	-- Add PMIDs to the publications temp table
  	INSERT  INTO #Publications
              ( PMID ,
  			  PMCID,
                EntityDate ,
                Reference ,
                Source ,
                URL ,
                Title ,
  			  Authors
              )
              SELECT -- Get Pub Med pubs
                      PG.PMID ,
  					PG.PMCID,
                      EntityDate = PG.PubDate,
                      Reference = REPLACE([UCSF.CTSASearch].[fnPublication.Pubmed.General2Reference](PG.PMID,
                                                                PG.ArticleDay,
                                                                PG.ArticleMonth,
                                                                PG.ArticleYear,
                                                                PG.ArticleTitle,
                                                                PG.Issue,
                                                                PG.JournalDay,
                                                                PG.JournalMonth,
                                                                PG.JournalYear,
                                                                PG.MedlineDate,
                                                                PG.MedlinePgn,
                                                                PG.MedlineTA,
                                                                PG.Volume, 0),
                                          CHAR(11), '') ,
                      Source = CASE 
  						when PG.pmid > 0 then 'PubMed'
  						else WEBPAGE.DBType
  					end,
  					URL = Case 
  						when PG.pmid > 0 then 'http://www.ncbi.nlm.nih.gov/pubmed/' + CAST(ISNULL(PG.pmid, '') AS VARCHAR(20))
  						else WEBPAGE.url
  					end,
                      Title = left((case when IsNull(PG.ArticleTitle,'') <> '' then PG.ArticleTitle else 'Untitled Publication' end),4000),
  					Authors = PG.Authors
              FROM    [Profile.Data].[Publication.PubMed.General] PG
  			join [UCSF.].[Publication.URL] WEBPAGE on PG.pmid=WEBPAGE.pmid
  			WHERE	PG.PMID IN (
  						SELECT PMID 
  						FROM [Profile.Data].[Publication.Person.Include]
  						WHERE PMID IS NOT NULL AND PersonID = @PersonID
  					)
  					AND PG.PMID NOT IN (
  						SELECT PMID
  						FROM [Profile.Data].[Publication.Entity.InformationResource]
  						WHERE PMID IS NOT NULL
  					)
   
  	-- Add MPIDs to the publications temp table
  	INSERT  INTO #Publications
              ( MPID ,
                EntityDate ,
  			  Reference ,
  			  Source ,
                URL ,
                Title ,
  			  Authors
              )
              SELECT  MPID ,
                      EntityDate ,
   
   
                       Reference = REPLACE((CASE WHEN IsNull(article,'') <> '' THEN article + '. ' ELSE '' END)
  										+ (CASE WHEN IsNull(pub,'') <> '' THEN pub + '. ' ELSE '' END)
  										+ y
                                          + CASE WHEN y <> ''
                                                      AND vip <> '' THEN '; '
                                                 ELSE ''
                                            END + vip
                                          + CASE WHEN y <> ''
                                                      OR vip <> '' THEN '.'
                                                 ELSE ''
                                            END, CHAR(11), '') ,
                      Source = 'Custom' ,
                      URL = url,
                      Title = left((case when IsNull(article,'')<>'' then article when IsNull(pub,'')<>'' then pub else 'Untitled Publication' end),4000),
  					Authors = authors 
              FROM    ( SELECT    MPID ,
                                  EntityDate ,
                                  url ,
                                  authors = CASE WHEN authors = '' THEN ''
                                                 WHEN RIGHT(authors, 1) = '.'
                                                 THEN LEFT(authors,
                                                           LEN(authors) - 1)
                                                 ELSE authors
                                            END ,
                                  article = CASE WHEN article = '' THEN ''
                                                 WHEN RIGHT(article, 1) = '.'
                                                 THEN LEFT(article,
                                                           LEN(article) - 1)
                                                 ELSE article
                                            END ,
                                  pub = CASE WHEN pub = '' THEN ''
                                             WHEN RIGHT(pub, 1) = '.'
                                             THEN LEFT(pub, LEN(pub) - 1)
                                             ELSE pub
                                        END ,
                                  y ,
                                  vip
                        FROM      ( SELECT    MPG.mpid ,
                                              EntityDate = MPG.publicationdt ,
                                              authors = CASE WHEN RTRIM(LTRIM(COALESCE(MPG.authors,
                                                                ''))) = ''
                                                             THEN ''
                                                             WHEN RIGHT(COALESCE(MPG.authors,
                                                                ''), 1) = '.'
                                                              THEN  COALESCE(MPG.authors,
                                                                '') + ' '
                                                             ELSE COALESCE(MPG.authors,
                                                                '') + '. '
                                                        END ,
                                              url = CASE WHEN COALESCE(MPG.url,
                                                                '') <> ''
                                                              AND LEFT(COALESCE(MPG.url,
                                                                ''), 4) = 'http'
                                                         THEN MPG.url
                                                         WHEN COALESCE(MPG.url,
                                                                '') <> ''
                                                         THEN 'http://' + MPG.url
                                                         ELSE ''
                                                    END ,
                                              article = LTRIM(RTRIM(COALESCE(MPG.articletitle,
                                                                ''))) ,
                                              pub = LTRIM(RTRIM(COALESCE(MPG.pubtitle,
                                                                ''))) ,
                                              y = CASE WHEN MPG.publicationdt > '1/1/1901'
                                                       THEN CONVERT(VARCHAR(50), YEAR(MPG.publicationdt))
                                                       ELSE ''
                                                  END ,
                                              vip = COALESCE(MPG.volnum, '')
                                              + CASE WHEN COALESCE(MPG.issuepub,
                                                                '') <> ''
                                                     THEN '(' + MPG.issuepub
                                                          + ')'
                                                     ELSE ''
                                                END
                                              + CASE WHEN ( COALESCE(MPG.paginationpub,
                                                                '') <> '' )
                                                          AND ( COALESCE(MPG.volnum,
                                                                '')
                                                                + COALESCE(MPG.issuepub,
                                                                '') <> '' )
                                                     THEN ':'
                                                     ELSE ''
                                                END + COALESCE(MPG.paginationpub,
                                                               '')
                                    FROM      [Profile.Data].[Publication.MyPub.General] MPG
                                    INNER JOIN [Profile.Data].[Publication.Person.Include] PL ON MPG.mpid = PL.mpid
                                                             AND PL.mpid NOT LIKE 'DASH%'
                                                             AND PL.mpid NOT LIKE 'ISI%'
                                                             AND PL.pmid IS NULL
                                                             AND PL.PersonID = @PersonID
  									WHERE MPG.MPID NOT IN (
  										SELECT MPID
  										FROM [Profile.Data].[Publication.Entity.InformationResource]
  										WHERE (MPID IS NOT NULL)
  									)
                                  ) T0
                      ) T0
   
  	CREATE NONCLUSTERED INDEX idx_pmid on #publications(pmid)
  	CREATE NONCLUSTERED INDEX idx_mpid on #publications(mpid)
  
  	----------------------------------------------------------------------
  	-- Update the Publication.Entity.InformationResource table
  	----------------------------------------------------------------------
   
  	-- Insert new publications
  	INSERT INTO [Profile.Data].[Publication.Entity.InformationResource] (
  			PMID,
  			PMCID,
  			MPID,
  			EntityName,
  			EntityDate,
  			Reference,
  			Source,
  			URL,
  			IsActive, 
  			Authors
  		)
  		SELECT 	PMID,
  				PMCID,
  				MPID,
  				Title,
  				EntityDate,
  				Reference,
  				Source,
  				URL,
  				1 IsActive,
  				Authors
  		FROM #publications
  	-- Assign an EntityName, PubYear, and YearWeight
  	UPDATE e
  		SET --e.EntityName = 'Publication ' + CAST(e.EntityID as VARCHAR(50)),
  			e.PubYear = year(e.EntityDate),
  			e.YearWeight = (case when e.EntityDate is null then 0.5
  							when year(e.EntityDate) <= 1901 then 0.5
  							else power(cast(0.5 as float),cast(datediff(d,e.EntityDate,GetDate()) as float)/365.25/10)
  							end),
  			e.Reference = p.Reference,
  			e.Authors = p.Authors
  		FROM [Profile.Data].[Publication.Entity.InformationResource] e,
  			#publications p
  		WHERE ((e.PMID = p.PMID) OR (e.MPID = p.MPID))
   
  	-- *******************************************************************
  	-- *******************************************************************
  	-- Update Authorship entities
  	-- *******************************************************************
  	-- *******************************************************************
   
   	----------------------------------------------------------------------
  	-- Get a list of current Authorship records
  	----------------------------------------------------------------------
  
  	CREATE TABLE #Authorship
  	(
  		EntityDate DATETIME NULL ,
  		authorRank INT NULL,
  		numberOfAuthors INT NULL,
  		authorNameAsListed VARCHAR(255) NULL,
  		AuthorWeight FLOAT NULL,
  		AuthorPosition VARCHAR(1) NULL,
  		PubYear INT NULL ,
  		YearWeight FLOAT NULL ,
  		PersonID INT NULL ,
  		InformationResourceID INT NULL,
  		PMID INT NULL,
  		IsActive BIT
  	)
   
  	INSERT INTO #Authorship (EntityDate, PersonID, InformationResourceID, PMID, IsActive)
  		SELECT e.EntityDate, i.PersonID, e.EntityID, e.PMID, 1 IsActive
  			FROM [Profile.Data].[Publication.Entity.InformationResource] e,
  				[Profile.Data].[Publication.Person.Include] i
  			WHERE (e.PMID = i.PMID) and (e.PMID is not null) and (i.PersonID = @PersonID)
  	INSERT INTO #Authorship (EntityDate, PersonID, InformationResourceID, PMID, IsActive)
  		SELECT e.EntityDate, i.PersonID, e.EntityID, null PMID, 1 IsActive
  			FROM [Profile.Data].[Publication.Entity.InformationResource] e,
  				[Profile.Data].[Publication.Person.Include] i
  			WHERE (e.MPID = i.MPID) and (e.MPID is not null) and (e.PMID is null) and (i.PersonID = @PersonID)
  	CREATE NONCLUSTERED INDEX idx_person_pmid ON #Authorship(PersonID, PMID)
  	CREATE NONCLUSTERED INDEX idx_person_pub ON #Authorship(PersonID, InformationResourceID)
   
  	UPDATE a
  		SET	a.authorRank=p.authorRank,
  			a.numberOfAuthors=p.numberOfAuthors,
  			a.authorNameAsListed=p.authorNameAsListed, 
  			a.AuthorWeight=p.AuthorWeight, 
  			a.AuthorPosition=p.AuthorPosition,
  			a.PubYear=p.PubYear,
  			a.YearWeight=p.YearWeight
  		FROM #Authorship a, [Profile.Cache].[Publication.PubMed.AuthorPosition]  p
  		WHERE a.PersonID = p.PersonID and a.PMID = p.PMID and a.PMID is not null
  	UPDATE #authorship
  		SET authorWeight = 0.5
  		WHERE authorWeight IS NULL
  	UPDATE #authorship
  		SET authorPosition = 'U'
  		WHERE authorPosition IS NULL
  	UPDATE #authorship
  		SET PubYear = year(EntityDate)
  		WHERE PubYear IS NULL
  	UPDATE #authorship
  		SET	YearWeight = (case when EntityDate is null then 0.5
  							when year(EntityDate) <= 1901 then 0.5
  							else power(cast(0.5 as float),cast(datediff(d,EntityDate,GetDate()) as float)/365.25/10)
  							end)
  		WHERE YearWeight IS NULL
  
  	----------------------------------------------------------------------
  	-- Update the Publication.Authorship table
  	----------------------------------------------------------------------
   
  	-- Set IsActive = 0 for Authorships that no longer exist
  	UPDATE [Profile.Data].[Publication.Entity.Authorship]
  		SET IsActive = 0
  		WHERE PersonID = @PersonID
  			AND InformationResourceID NOT IN (SELECT InformationResourceID FROM #authorship)
  	-- Set IsActive = 1 for current Authorships and update data
  	UPDATE e
  		SET e.EntityDate = a.EntityDate,
  			e.authorRank = a.authorRank,
  			e.numberOfAuthors = a.numberOfAuthors,
  			e.authorNameAsListed = a.authorNameAsListed,
  			e.authorWeight = a.authorWeight,
  			e.authorPosition = a.authorPosition,
  			e.PubYear = a.PubYear,
  			e.YearWeight = a.YearWeight,
  			e.IsActive = 1
  		FROM #authorship a, [Profile.Data].[Publication.Entity.Authorship] e
  		WHERE a.PersonID = e.PersonID and a.InformationResourceID = e.InformationResourceID
  	-- Insert new Authorships
  	INSERT INTO [Profile.Data].[Publication.Entity.Authorship] (
  			EntityDate,
  			authorRank,
  			numberOfAuthors,
  			authorNameAsListed,
  			authorWeight,
  			authorPosition,
  			PubYear,
  			YearWeight,
  			PersonID,
  			InformationResourceID,
  			IsActive
  		)
  		SELECT 	EntityDate,
  				authorRank,
  				numberOfAuthors,
  				authorNameAsListed,
  				authorWeight,
  				authorPosition,
  				PubYear,
  				YearWeight,
  				PersonID,
  				InformationResourceID,
  				IsActive
  		FROM #authorship a
  		WHERE NOT EXISTS (
  			SELECT *
  			FROM [Profile.Data].[Publication.Entity.Authorship] e
  			WHERE a.PersonID = e.PersonID and a.InformationResourceID = e.InformationResourceID
  		)
  	-- Assign an EntityName
  	UPDATE [Profile.Data].[Publication.Entity.Authorship]
  		SET EntityName = 'Authorship ' + CAST(EntityID as VARCHAR(50))
  		WHERE PersonID = @PersonID AND EntityName is null
  
  
  	-- *******************************************************************
  	-- *******************************************************************
  	-- Update RDF
  	-- *******************************************************************
  	-- *******************************************************************
  
  
  
  	--------------------------------------------------------------
  	-- Version 3 : Create stub RDF
  	--------------------------------------------------------------
  
  	CREATE TABLE #sql (
  		i INT IDENTITY(0,1) PRIMARY KEY,
  		s NVARCHAR(MAX)
  	)
  	INSERT INTO #sql (s)
  		SELECT	'EXEC [RDF.Stage].ProcessDataMap '
  					+'  @DataMapID = '+CAST(DataMapID AS VARCHAR(50))
  					+', @InternalIdIn = '+InternalIdIn
  					+', @TurnOffIndexing=0, @SaveLog=0; '
  		FROM (
  			SELECT *, '''SELECT CAST(EntityID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#Authorship'
  					AND NetworkProperty IS NULL
  					AND Property IS NULL
  			UNION ALL
  			SELECT *, '''' + CAST(@PersonID AS VARCHAR(50)) + '''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://xmlns.com/foaf/0.1/Person' 
  					AND property = 'http://vivoweb.org/ontology/core#authorInAuthorship'
  					AND NetworkProperty IS NULL
  		) t
  		ORDER BY DataMapID
  
  	DECLARE @s NVARCHAR(MAX)
  	WHILE EXISTS (SELECT * FROM #sql)
  	BEGIN
  		SELECT @s = s
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  		print @s
  		EXEC sp_executesql @s
  		DELETE
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  	END
  
  	--select * from [Ontology.].DataMap
  
  
  /*
  
  	--------------------------------------------------------------
  	-- Version 1 : Create all RDF using ProcessDataMap
  	--------------------------------------------------------------
  
  	CREATE TABLE #sql (
  		i INT IDENTITY(0,1) PRIMARY KEY,
  		s NVARCHAR(MAX)
  	)
  	INSERT INTO #sql (s)
  		SELECT	'EXEC [RDF.Stage].ProcessDataMap '
  					+'  @DataMapID = '+CAST(DataMapID AS VARCHAR(50))
  					+', @InternalIdIn = '+InternalIdIn
  					+', @TurnOffIndexing=0, @SaveLog=0; '
  		FROM (
  			SELECT *, '''SELECT CAST(InformationResourceID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#InformationResource' 
  					AND IsNull(property,'') <> 'http://vivoweb.org/ontology/core#informationResourceInAuthorship'
  					AND NetworkProperty IS NULL
  			UNION ALL
  			SELECT *, '''SELECT CAST(EntityID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#Authorship'
  					AND IsNull(property,'') NOT IN ('http://vivoweb.org/ontology/core#linkedAuthor','http://vivoweb.org/ontology/core#linkedInformationResource')
  					AND NetworkProperty IS NULL
  			UNION ALL
  			SELECT *, '''SELECT CAST(InformationResourceID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#InformationResource' 
  					AND property = 'http://vivoweb.org/ontology/core#informationResourceInAuthorship'
  					AND NetworkProperty IS NULL
  			UNION ALL
  			SELECT *, '''' + CAST(@PersonID AS VARCHAR(50)) + '''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://xmlns.com/foaf/0.1/Person' 
  					AND property = 'http://vivoweb.org/ontology/core#authorInAuthorship'
  					AND NetworkProperty IS NULL
  		) t
  		ORDER BY DataMapID
  
  	DECLARE @s NVARCHAR(MAX)
  	WHILE EXISTS (SELECT * FROM #sql)
  	BEGIN
  		SELECT @s = s
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  		--print @s
  		EXEC sp_executesql @s
  		DELETE
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  	END
  
  */
  
  
  /*
  
  	---------------------------------------------------------------------------------
  	-- Version 2 : Create new entities using ProcessDataMap, and triples manually
  	---------------------------------------------------------------------------------
  
  	CREATE TABLE #sql (
  		i INT IDENTITY(0,1) PRIMARY KEY,
  		s NVARCHAR(MAX)
  	)
  	INSERT INTO #sql (s)
  		SELECT	'EXEC [RDF.Stage].ProcessDataMap '
  					+'  @DataMapID = '+CAST(DataMapID AS VARCHAR(50))
  					+', @InternalIdIn = '+InternalIdIn
  					+', @TurnOffIndexing=0, @SaveLog=0; '
  		FROM (
  			SELECT *, '''SELECT CAST(InformationResourceID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#InformationResource' 
  					AND IsNull(property,'') <> 'http://vivoweb.org/ontology/core#informationResourceInAuthorship'
  					AND NetworkProperty IS NULL
  			UNION ALL
  			SELECT *, '''SELECT CAST(EntityID AS VARCHAR(50)) FROM [Profile.Data].[Publication.Entity.Authorship] WHERE PersonID = '+CAST(@PersonID AS VARCHAR(50))+'''' InternalIdIn
  				FROM [Ontology.].DataMap
  				WHERE class = 'http://vivoweb.org/ontology/core#Authorship'
  					AND IsNull(property,'') NOT IN ('http://vivoweb.org/ontology/core#linkedAuthor','http://vivoweb.org/ontology/core#linkedInformationResource')
  					AND NetworkProperty IS NULL
  		) t
  		ORDER BY DataMapID
  
  	--select * from #sql
  	--return
  
  	DECLARE @s NVARCHAR(MAX)
  	WHILE EXISTS (SELECT * FROM #sql)
  	BEGIN
  		SELECT @s = s
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  		--print @s
  		EXEC sp_executesql @s
  		DELETE
  			FROM #sql
  			WHERE i = (SELECT MIN(i) FROM #sql)
  	END
  
  
  	CREATE TABLE #a (
  		PersonID INT,
  		AuthorshipID INT,
  		InformationResourceID INT,
  		IsActive BIT,
  		PersonNodeID BIGINT,
  		AuthorshipNodeID BIGINT,
  		InformationResourceNodeID BIGINT,
  		AuthorInAuthorshipTripleID BIGINT,
  		LinkedAuthorTripleID BIGINT,
  		LinkedInformationResourceTripleID BIGINT,
  		InformationResourceInAuthorshipTripleID BIGINT,
  		AuthorRank INT,
  		EntityDate DATETIME,
  		TripleWeight FLOAT,
  		AuthorRecord INT
  	)
  	-- Get authorship records
  	INSERT INTO #a (PersonID, AuthorshipID, InformationResourceID, IsActive, AuthorRank, EntityDate, TripleWeight, AuthorRecord)
  		SELECT PersonID, EntityID, InformationResourceID, IsActive, 
  				AuthorRank, EntityDate, IsNull(authorweight * yearweight,0),
  				0
  			FROM [Profile.Data].[Publication.Entity.Authorship]
  			WHERE PersonID = @PersonID
  		UNION ALL
  		SELECT PersonID, EntityID, InformationResourceID, IsActive, 
  				AuthorRank, EntityDate, IsNull(authorweight * yearweight,0),
  				1
  			FROM [Profile.Data].[Publication.Entity.Authorship]
  			WHERE PersonID <> @PersonID 
  				AND IsActive = 1
  				AND InformationResourceID IN (
  					SELECT InformationResourceID
  					FROM [Profile.Data].[Publication.Entity.Authorship]
  					WHERE PersonID = @PersonID
  				)
  	-- Get entity IDs
  	UPDATE a
  		SET a.PersonNodeID = m.NodeID
  		FROM #a a, [RDF.Stage].InternalNodeMap m
  		WHERE m.Class = 'http://xmlns.com/foaf/0.1/Person'
  			AND m.InternalType = 'Person'
  			AND m.InternalID = CAST(a.PersonID AS VARCHAR(50))
  	UPDATE a
  		SET a.AuthorshipNodeID = m.NodeID
  		FROM #a a, [RDF.Stage].InternalNodeMap m
  		WHERE m.Class = 'http://vivoweb.org/ontology/core#Authorship'
  			AND m.InternalType = 'Authorship'
  			AND m.InternalID = CAST(a.AuthorshipID AS VARCHAR(50))
  	UPDATE a
  		SET a.InformationResourceNodeID = m.NodeID
  		FROM #a a, [RDF.Stage].InternalNodeMap m
  		WHERE m.Class = 'http://vivoweb.org/ontology/core#InformationResource'
  			AND m.InternalType = 'InformationResource'
  			AND m.InternalID = CAST(a.InformationResourceID AS VARCHAR(50))
  	-- Get triple IDs
  	UPDATE a
  		SET a.AuthorInAuthorshipTripleID = t.TripleID
  		FROM #a a, [RDF.].Triple t
  		WHERE a.PersonNodeID IS NOT NULL AND a.AuthorshipNodeID IS NOT NULL
  			AND t.subject = a.PersonNodeID
  			AND t.predicate = [RDF.].fnURI2NodeID('http://vivoweb.org/ontology/core#authorInAuthorship')
  			AND t.object = a.AuthorshipNodeID
  	UPDATE a
  		SET a.LinkedAuthorTripleID = t.TripleID
  		FROM #a a, [RDF.].Triple t
  		WHERE a.PersonNodeID IS NOT NULL AND a.AuthorshipNodeID IS NOT NULL
  			AND t.subject = a.AuthorshipNodeID
  			AND t.predicate = [RDF.].fnURI2NodeID('http://vivoweb.org/ontology/core#linkedAuthor')
  			AND t.object = a.PersonNodeID
  	UPDATE a
  		SET a.LinkedInformationResourceTripleID = t.TripleID
  		FROM #a a, [RDF.].Triple t
  		WHERE a.AuthorshipNodeID IS NOT NULL AND a.InformationResourceID IS NOT NULL
  			AND t.subject = a.AuthorshipNodeID
  			AND t.predicate = [RDF.].fnURI2NodeID('http://vivoweb.org/ontology/core#linkedInformationResource')
  			AND t.object = a.InformationResourceNodeID
  	UPDATE a
  		SET a.InformationResourceInAuthorshipTripleID = t.TripleID
  		FROM #a a, [RDF.].Triple t
  		WHERE a.AuthorshipNodeID IS NOT NULL AND a.InformationResourceID IS NOT NULL
  			AND t.subject = a.InformationResourceNodeID
  			AND t.predicate = [RDF.].fnURI2NodeID('http://vivoweb.org/ontology/core#informationResourceInAuthorship')
  			AND t.object = a.AuthorshipNodeID
  	
  	--select * from #a
  	--return
  	--select * from [ontology.].datamap
  
  
  
  	SELECT a.IsActive, a.subject, m._PropertyNode predicate, a.object, 
  			a.TripleWeight, 0 ObjectType, a.SortOrder,
  			IsNull(s.ViewSecurityGroup, m.ViewSecurityGroup) ViewSecurityGroup,
  			a.TripleID, t.SortOrder ExistingSortOrder, X
  		INTO #b
  		FROM (
  				SELECT AuthorshipNodeID subject, InformationResourceNodeID object, TripleWeight, 
  						'http://vivoweb.org/ontology/core#Authorship' Class,
  						'http://vivoweb.org/ontology/core#linkedInformationResource' Property,
  						1 SortOrder,
  						IsActive,
  						LinkedInformationResourceTripleID TripleID,
  						1 X
  					FROM #a
  					WHERE AuthorRecord = 0
  					--WHERE IsActive = 1
  				UNION ALL
  				SELECT AuthorshipNodeID subject, PersonNodeID object, 1 TripleWeight,
  						'http://vivoweb.org/ontology/core#Authorship' Class,
  						'http://vivoweb.org/ontology/core#linkedAuthor' Property,
  						1 SortOrder,
  						IsActive,
  						LinkedAuthorTripleID TripleID,
  						2 X
  					FROM #a
  					WHERE AuthorRecord = 0
  					--WHERE IsActive = 1
  				UNION ALL
  				SELECT InformationResourceNodeID subject, AuthorshipNodeID object, TripleWeight, 
  						'http://vivoweb.org/ontology/core#InformationResource' Class,
  						'http://vivoweb.org/ontology/core#informationResourceInAuthorship' Property,
  						row_number() over (partition by InformationResourceNodeID, IsActive order by AuthorRank, t.SortOrder, AuthorshipNodeID) SortOrder,
  						IsActive,
  						InformationResourceInAuthorshipTripleID TripleID,
  						3 X
  					FROM #a a
  						LEFT OUTER JOIN [RDF.].[Triple] t
  						ON a.InformationResourceInAuthorshipTripleID = t.TripleID
  					--WHERE IsActive = 1
  				UNION ALL
  				SELECT PersonNodeID subject, AuthorshipNodeID object, 1 TripleWeight, 
  						'http://xmlns.com/foaf/0.1/Person' Class,
  						'http://vivoweb.org/ontology/core#authorInAuthorship' Property,
  						row_number() over (partition by PersonNodeID, IsActive order by EntityDate desc) SortOrder,
  						IsActive,
  						AuthorInAuthorshipTripleID TripleID,
  						4 X
  					FROM #a
  					WHERE AuthorRecord = 0
  					--WHERE IsActive = 1
  			) a
  			INNER JOIN [Ontology.].[DataMap] m
  				ON m.Class = a.Class AND m.NetworkProperty IS NULL AND m.Property = a.Property
  			LEFT OUTER JOIN [RDF.].[Triple] t
  				ON a.TripleID = t.TripleID
  			LEFT OUTER JOIN [RDF.Security].[NodeProperty] s
  				ON s.NodeID = a.subject
  					AND s.Property = m._PropertyNode
  
  	--SELECT * FROM #b ORDER BY X, subject, property, IsActive, sortorder
  
  	-- Delete
  	DELETE
  		FROM [RDF.].Triple
  		WHERE TripleID IN (
  			SELECT TripleID
  			FROM #b
  			WHERE IsActive = 0 AND TripleID IS NOT NULL
  		)
  	--select @@ROWCOUNT
  
  	-- Update
  	UPDATE t
  		SET t.SortOrder = b.SortOrder
  		FROM [RDF.].Triple t
  			INNER JOIN #b b
  			ON t.TripleID = b.TripleID
  				AND b.IsActive = 1 
  				AND b.TripleID IS NOT NULL
  				AND b.SortOrder <> b.ExistingSortOrder
  	--select @@ROWCOUNT
  
  	-- Insert
  	INSERT INTO [RDF.].Triple (Subject,Predicate,Object,TripleHash,Weight,Reitification,ObjectType,SortOrder,ViewSecurityGroup,Graph)
  		SELECT Subject,Predicate,Object,
  				[RDF.].fnTripleHash(Subject,Predicate,Object),
  				TripleWeight,NULL,0,SortOrder,ViewSecurityGroup,1
  			FROM #b
  			WHERE IsActive = 1 AND TripleID IS NULL
  	--select @@ROWCOUNT
  
  */
  
  
  END
  
  
  
  
  
  
  GO
  
  
  