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

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

CREATE PROCEDURE [dbo].[GetContentAttributeData] @iContentTypeId INT
AS
BEGIN TRY
	DECLARE @cGetID CURSOR
	DECLARE @cGetProcessedData CURSOR
	DECLARE @iContentDescriptorId INT
	DECLARE @sContentAttributeType VARCHAR(256)
	DECLARE	@iImageContentId INT
	DECLARE @sImageContentTitle VARCHAR(256)
	DECLARE @sImageFileName VARCHAR(50)
	DECLARE @sThumbnailFileName VARCHAR(50)
	DECLARE @sContentAttributeId VARCHAR(8000)
	DECLARE @sContentAttributeValue NVARCHAR(MAX)
	DECLARE @iPrevContentDescriptorId INT
	DECLARE @sAttributeTypeColumnName VARCHAR(3)
	DECLARE @SQL NVARCHAR(MAX)

	SET @sContentAttributeValue = ''
	SET @iPrevContentDescriptorId = 0

	-- create temporary tables
	CREATE TABLE #DataProcess
	(
		ContentDescriptorId INT,
		ContentAttributeType VARCHAR(50),
		ImageContentId INT,
		ImageContentTitle VARCHAR(256),
		ImageFileName VARCHAR(50),
		ThumbnailFileName VARCHAR(50),
		ContentAttributeIds VARCHAR(8000),
		ContentAttributeValues NVARCHAR(MAX)
	)

	CREATE TABLE #XMLData
	(
		id INT,
		icId INT,
		tl VARCHAR(256),
		cp VARCHAR(50),
		ti VARCHAR(50),
		bs NVARCHAR(MAX),
		br NVARCHAR(MAX),
		vo NVARCHAR(MAX),
		ms NVARCHAR(MAX),
		im NVARCHAR(MAX),
		sm NVARCHAR(MAX),
		lsm NVARCHAR(MAX),
		gr VARCHAR(6)
	)

	-- define the forward only, read-only cursor 
	SET @cGetID = CURSOR FAST_FORWARD 
	FOR
	SELECT cd.Id, cat.Name, ic.Id as icId, ic.Title, ic.FileName, ic.ThumbnailFileName, left(aval,LEN(aval)-1) as aval
	 FROM 
	  ContentDescriptor cd 
	  INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y'
	  INNER JOIN ContentDescriptorToAttributeMap cdmap ON cd.id = cdmap.ContentDescriptorId AND cd.ContentTypeId = @iContentTypeId
	  INNER JOIN ContentAttribute ca ON ca.id = cdmap.ContentAttributeId
	  INNER JOIN ContentAttributeType cat ON ca.AttributeTypeId = cat.id AND 
	  cat.Name IN ('Body System','Body Region','View Orientation','Medical Specialty','Gender','Image Type','Description','Long Description') 
	  CROSS APPLY (SELECT CAST(ca1.id as VARCHAR)+','
	 FROM 
	  ContentDescriptorToAttributeMap cdmap1 
	  INNER JOIN ContentAttribute ca1 ON ca1.id = cdmap1.ContentAttributeId
	  INNER JOIN ContentAttributeType cat1 ON ca1.AttributetypeId = cat1.id AND cat1.id=cat.id
	  WHERE cdmap1.ContentDescriptorId = cd.id FOR XML PATH('')) pre_trimmed(aval)
	  GROUP BY cd.id,cat.name,ic.Id,ic.Title,ic.FileName,ic.ThumbnailFileName,aval 

	-- open & fetch the cursor variables into the local variables
	OPEN @cGetID 
	FETCH NEXT FROM @cGetID INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle,
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId

	-- start of while loop
	 WHILE @@FETCH_STATUS = 0
	  BEGIN
		SET @sContentAttributeValue = ''
		
		SELECT @sContentAttributeValue = CONVERT(NVARCHAR(MAX), ContentAttribute.AttributeValue) + ', ' +@sContentAttributeValue
		FROM ContentAttribute WHERE ContentAttribute.Id IN (SELECT item from dbo.fnSplit(@sContentAttributeId,',')) 
		order by CONVERT(NVARCHAR(MAX), ContentAttribute.AttributeValue) DESC
		IF LEN(@sContentAttributeValue) > 0
		BEGIN
			SET @sContentAttributeValue = SUBSTRING(@sContentAttributeValue,1,LEN(@sContentAttributeValue)-1)
		END
		INSERT INTO #DataProcess(ContentDescriptorId, ContentAttributeType, ImageContentId, ImageContentTitle,
			ImageFileName, ThumbnailFileName, ContentAttributeIds, ContentAttributeValues)	
		VALUES(@iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
			@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue)

		FETCH NEXT FROM @cGetID INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle,
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId
	-- end of while loop
	END
	-- close the cursor to free up resources
	CLOSE @cGetID
	DEALLOCATE @cGetID

	SET @cGetProcessedData = CURSOR FAST_FORWARD 
	FOR
	SELECT ContentDescriptorId, ContentAttributeType, ImageContentId, ImageContentTitle, ImageFileName, 
		ThumbnailFileName, ContentAttributeIds, ContentAttributeValues FROM #DataProcess ORDER BY ContentDescriptorId
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetProcessedData 
	FETCH NEXT FROM @cGetProcessedData INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue
	WHILE @@FETCH_STATUS = 0
	  BEGIN
		SET @SQL = ''
		SET @sAttributeTypeColumnName = CASE @sContentAttributeType WHEN 'Body System' THEN 'bs'
			WHEN 'Body Region' THEN 'br'
			WHEN 'View Orientation' THEN 'vo'
			WHEN 'Medical Specialty' THEN 'ms'
			WHEN 'Gender' THEN 'gr'
			WHEN 'Image Type' THEN 'im'
			WHEN 'Description' THEN 'sm'
			WHEN 'Long Description' THEN 'lsm'
			END
		-- to handle the single quotes in the attribute value	
		SET @sContentAttributeValue = REPLACE(@sContentAttributeValue,'''','''''')
		SET @sImageContentTitle = REPLACE(@sImageContentTitle,'''','''''')
		
		IF @iContentDescriptorId != @iPrevContentDescriptorId
		BEGIN
			SET @SQL = 'INSERT INTO #XMLData (id, icId, tl, cp, ti, '+@sAttributeTypeColumnName+')
			VALUES('+CONVERT(VARCHAR(20),@iContentDescriptorId)+','''+CONVERT(VARCHAR(20),@iImageContentId)+''','''+@sImageContentTitle+'''
			,'''+@sImageFileName+''','''+@sThumbnailFileName+''','''+@sContentAttributeValue+''')'
		END
		ELSE
		BEGIN
			SET @SQL = 'UPDATE #XMLData SET '+@sAttributeTypeColumnName+' = '''+@sContentAttributeValue+''' WHERE id = '+CONVERT(VARCHAR(20),@iContentDescriptorId)
		END
		
		-- to execute the dynamic query
		EXEC sp_executesql @SQL
		SET @iPrevContentDescriptorId = @iContentDescriptorId
		
		FETCH NEXT FROM @cGetProcessedData INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue

	END
	SELECT * FROM #XMLData FOR XML RAW ('it')
	-- close the cursor to free up resources
	CLOSE @cGetProcessedData
	DEALLOCATE @cGetProcessedData
	-- drop the temporary tables
	DROP TABLE #XMLData
	DROP TABLE #DataProcess
END TRY
BEGIN CATCH
		SELECT Error_Message() as SPStatus
END CATCH


GO