﻿USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetImagePinData]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- alter script of GetImagePinData SP

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

CREATE PROCEDURE [dbo].[GetImagePinData]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
DECLARE @cGetImageContentId CURSOR
DECLARE @iImageContentId INT
DECLARE @xmlData VARCHAR(MAX)

TRUNCATE TABLE TempXMLStore
SET @cGetImageContentId = CURSOR FAST_FORWARD
	FOR
		SELECT  ic.Id
		FROM ContentDescriptor cd 
		INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y' AND cd.ContentTypeId = 2
	  
	OPEN @cGetImageContentId
	FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?>'
		SET @xmlData = @xmlData+(SELECT Tag, Parent, [root!1!aoi], [it!2!pId], [it!2!hX], [it!2!hY], [it!2!pX], 
			[it!2!pY], [it!2!tId], [it!2!bsId], [it!2!bs] 
			FROM (SELECT DISTINCT 1 AS Tag, NULL AS Parent,
			aoi.FileName AS [root!1!aoi],
			NULL AS [it!2!pId],
			NULL AS [it!2!hX],
			NULL AS [it!2!hY],
			NULL AS [it!2!pX],
			NULL AS [it!2!pY],
			NULL AS [it!2!tId],
			NULL AS [it!2!bsId],
			NULL AS [it!2!bs]
		FROM AtlasOrientImage aoi
		RIGHT JOIN ImageContent ic ON aoi.ContentDescriptorId = ic.ContentDescriptorId
		WHERE ic.Id = @iImageContentId
		UNION ALL
		SELECT DISTINCT 2 AS Tag, 1 AS Parent,
			aoi1.FileName AS [root!1!aoi],
			ap.Id AS [it!2!pId],
			ap.HeadX AS [it!2!hX],
			ap.HeadY AS [it!2!hY],
			ap.PointX AS [it!2!pX],
			ap.PointY AS [it!2!pY],
			ap.VocabTermNumbers AS [it!2!tId],
			vtn.VocabSystemsNumbersId AS [it!2!bsId],
			vs.Name AS [it!2!bs]
		FROM AnnotationPin ap
		INNER JOIN ImageContent ic ON ap.ImageContentId = ic.Id
		INNER JOIN VocabTermNumbers vtn ON ap.VocabTermNumbers = vtn.TermNumber
		INNER JOIN VocabSystems vs ON vs.SystemNumber = vtn.VocabSystemsNumbersId
		LEFT JOIN AtlasOrientImage aoi1 ON aoi1.ContentDescriptorId = ic.ContentDescriptorId 
			WHERE ImageContentId = @iImageContentId ) as A
		ORDER BY [root!1!aoi],[it!2!pId] FOR XML Explicit)
		
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId
	END
END

GO
