USE [DemoDB]
GO
/****** Object:  StoredProcedure [dbo].[GetVPDashboardWidgetData]    Script Date: 11/21/2014 11:29:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetVPDashboardWidgetData]
 	@UserId Integer,
 	@DashboardUserWidgetId Integer
 AS
 	BEGIN

 				SELECT DISTINCT dlz.hMy   'ZoneId',
 			   dw.hMy	'WidgetId',
 			    d.hMy				'DashboardId',
 			   CONVERT(INT, d.hDashboardTypeId)		'DashboardType',
 			   dw.sWidgetCode				'WidgetCode',
 			   w.sName				'WidgetName',
 			   w.sDesc				'WidgetDescription',
 			   dw.iSequence					'WidgetSequence',
 			   w.sVertical			'WidgetVertical',
			   w.sSubCategory       'WidgetSubCategory',
 			   w.bCustom			'WidgetCustom',
 			   w.bTemplate			'WidgetTemplate',
 			   w.hWidgetType		'WidgetType',
 			   w.sName				'Title'
 		FROM   VPDashboard d
 			   INNER JOIN VPDashboardTile dt
 				 ON dt.hDashboardId = d.hMy
 			   INNER JOIN VPLayout l
 				 ON l.sCode = dt.sLayoutCode
 			   INNER JOIN VPDashboardZone dlz
 				 ON dlz.hDashboardTileId = dt.hMy
 			   INNER JOIN VPLayoutZone lz
 				 ON lz.sZoneCode = dlz.sZoneCode
 			   INNER JOIN VPDashboardWidget dw
 				 ON dw.hDashboardZoneId = dlz.hMy
 			   INNER JOIN VPWidget w
 				 ON w.sCode = dw.sWidgetCode
 			WHERE  dw.hMy = @DashboardUserWidgetId

 		SELECT DISTINCT ws.sWidgetCode 'WidgetCode',
 			ws.sSettingName 'SettingName',
 			ws.sSettingValue 'SettingValue'
 		FROM   VPDashboard d
 			   INNER JOIN VPDashboardTile dt
 				 ON dt.hDashboardId = d.hMy
 			   INNER JOIN VPLayout l
 				 ON l.sCode = dt.sLayoutCode
 			   INNER JOIN VPDashboardZone dlz
 				 ON dlz.hDashboardTileId = dt.hMy
 			   INNER JOIN VPLayoutZone lz
 				 ON lz.sZoneCode = dlz.sZoneCode
 			   INNER JOIN VPDashboardWidget dw
 				 ON dw.hDashboardZoneId = dlz.hMy
 			   INNER JOIN VPWidget w
 				 ON w.sCode = dw.sWidgetCode
 			   INNER JOIN VPWidgetSetting ws
 				ON ws.sWidgetCode = w.sCode
 			WHERE  dw.hMy = @DashboardUserWidgetId
 	select  DISTINCT wdp.sWidgetCode 'WidgetCode',
 				wdp.hMy 'Id',
 				wdp.sTableName 'TableName',
 				wdp.sColumnName 'ColumnName',
 				wdp.sDisplayName 'DisplayName',
 				wdp.iSequence 'Sequence',
 				wdp.sDrilldownWidgetCode 'DrilldownWidgetCode',
 				w2.sName 'DrilldownWidgetTitle',
 				wdp.sDrilldownGroupByTable 'DrilldownGroupByTable',
 				wdp.sDrilldownGroupByColumn 'DrilldownGroupByColumn'
 		FROM   VPDashboard d
 			   INNER JOIN VPDashboardTile dt
 				 ON dt.hDashboardId = d.hMy AND ((d.bUseTiles = '1' AND NOT dt.sTileCode IS NULL) OR (d.bUseTiles = '0' AND dt.sTileCode IS NULL))
 			   INNER JOIN VPLayout l
 				 ON l.sCode = dt.sLayoutCode
 			   INNER JOIN VPDashboardZone dlz
 				 ON dlz.hDashboardTileId = dt.hMy
 			   INNER JOIN VPLayoutZone lz
 				 ON lz.sZoneCode = dlz.sZoneCode
 			   INNER JOIN VPDashboardWidget dw
 				 ON dw.hDashboardZoneId = dlz.hMy
 			   INNER JOIN VPWidget w
 				 ON w.sCode = dw.sWidgetCode
 			   INNER JOIN VPWidgetDataPoint wdp
 				ON wdp.sWidgetCode = w.sCode
 				 LEFT JOIN VPWidget w2
 					ON w2.sCode = wdp.sDrilldownWidgetCode
 			WHERE  dw.hMy = @DashboardUserWidgetId
 select  DISTINCT wdps.hWidgetDataPoint 'WidgetDataPointId',
 				wdps.sSettingName 'SettingName',
 				wdps.sSettingValue 'SettingValue'
 		FROM   VPDashboard d
 			   INNER JOIN VPDashboardTile dt
 				 ON dt.hDashboardId = d.hMy AND ((d.bUseTiles = '1' AND NOT dt.sTileCode IS NULL) OR (d.bUseTiles = '0' AND dt.sTileCode IS NULL))
 			   INNER JOIN VPLayout l
 				 ON l.sCode = dt.sLayoutCode
 			   INNER JOIN VPDashboardZone dlz
 				 ON dlz.hDashboardTileId = dt.hMy
 			   INNER JOIN VPLayoutZone lz
 				 ON lz.sZoneCode = dlz.sZoneCode
 			   INNER JOIN VPDashboardWidget dw
 				 ON dw.hDashboardZoneId = dlz.hMy
 			   INNER JOIN VPWidget w
 				 ON w.sCode = dw.sWidgetCode
 			   INNER JOIN VPWidgetDataPoint wdp
 				ON wdp.sWidgetCode = w.sCode
 				INNER JOIN VPWidgetDataPointSetting wdps
 					ON wdps.hWidgetDataPoint = wdp.hMy
 			WHERE  dw.hMy = @DashboardUserWidgetId
 		SELECT DISTINCT dw.hMy   				'WidgetId',
 			   es.sSettingName							'EditorSettingName',
 			   es.sSettingValue				'EditorSettingValue'
 		FROM   VPDashboardTile dt
 			   INNER JOIN VPDashboardZone dlz
 				 ON dlz.hDashboardTileId = dt.hMy
 			   INNER JOIN VPDashboardWidget dw
 				 ON dw.hDashboardZoneId = dlz.hMy
 			   INNER JOIN VPEditorSettings es
 				 ON es.hDashboardWidget = dw.hMy
 			WHERE  es.hUserId = @UserId
 			   AND dw.hMy = @DashboardUserWidgetId
 	END