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

-- =============================================
-- Author:		magic
-- Create date: 17/4/2009
-- Description:	Fetch Layer  Data for given body view id and gender
-- =============================================

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

CREATE PROCEDURE [dbo].[DA_GetLayerModel] 
	-- Add the parameters for the stored procedure here
@body_view_id int , 
@sex char 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT	dl.Id , dl.LayerNumber , dl.LayerNumberInternal , dl.LayerName , 
			ISNULL(dl.IsSkin, 'N') AS IsSkin ,
			ISNULL(dl.IsBase, 'N')  AS IsBase , map.DissectibleRegionId , 
			map.LayerNumberInternal ,  art.Zoom ,
			ISNULL(art1.ImageId, 0)   AS ISOID , 
			art.ImageId AS COMPID
			FROM DissectibleRegionLayerArt art INNER JOIN
			DissectibleRegionLayerMapping map ON art.DissectibleRegionLayerMappingId = map.Id INNER JOIN
			DissectibleLayer dl ON map.DissectibleLayerId = dl.Id LEFT OUTER JOIN
			DissectibleRegionLayerArt art1 ON map.Id = art1.DissectibleRegionLayerMappingId AND art1.IsIsolate = 'Y' AND art1.IsBetadine <> 'Y' AND 
			art1.IsDermatome <> 'Y' AND art1.IsBald <> 'Y' AND art1.IsHair <> 'Y'
			WHERE     (map.BodyViewId = @body_view_id) AND (ISNULL(map.Sex, '+') = @sex) AND (art.IsBetadine <> 'Y') AND (art.IsDermatome <> 'Y') AND (art.IsBald <> 'Y') AND 
			(art.IsHair <> 'Y') AND (art.IsIsolate = 'N')
			ORDER BY dl.Id DESC, dl.LayerNumber DESC, map.DissectibleRegionId DESC 
			
END

GO