-- MetricIntegrationExample
-- Drop temporary tables, if they exist.
IF OBJECT_ID('tempdb..#BaseUrl') IS NOT NULL
	DROP TABLE #BaseUrl;
SELECT 'http://localhost/QPR2017-1/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp' AS VAL INTO #BaseUrl;

-- Drop temporary tables, if they exist.
IF OBJECT_ID('tempdb..#LogOnName') IS NOT NULL
	DROP TABLE #LogOnName;
SELECT 'qpr' AS VAL INTO #LogOnName;

-- Drop temporary tables, if they exist.
IF OBJECT_ID('tempdb..#Password') IS NOT NULL
	DROP TABLE #Password;
SELECT 'demo' AS VAL INTO #Password;

-- Drop temporary tables, if they exist.
IF OBJECT_ID('tempdb..#MetricsModelName') IS NOT NULL
	DROP TABLE #MetricsModelName;
SELECT 'Dentorex Group Scorecard' AS VAL INTO #MetricsModelName

-- Create a table for setting new values.
CREATE TABLE #METRICSVALUES
(
	Id Int NOT NULL IDENTITY(1,1),
	Scorecard VARCHAR(80),
	Element VARCHAR(80),
	Series VARCHAR(3),
	Period VARCHAR(20),
	Value VARCHAR(20)
);

INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q1/2014', '201');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q2/2014', '235');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q3/2014', '247');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q4/2014', '199');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q1/2015', '230');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q2/2015', '251');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q3/2015', '260');
INSERT INTO #METRICSVALUES (Scorecard, Element, Series, Period, Value)
 VALUES ('DENGROUP', 'ECOMMERCESALES', 'ACT', '$Q4/2015', '217');

SELECT * FROM [#METRICSVALUES];
(SELECT 'Title', 'MetricsData') UNION ALL
(SELECT 'SheetName', 'MetricsData') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
 
-------------------------------------------------------------
-- Setup Metrics connection
-------------------------------------------------------------
 (SELECT 'ScriptId', '17')	-- ID for SetupMetricsConnection
--#Run

-- Set values that are specific for UpdateMetricsValues
IF OBJECT_ID('tempdb..#MetricsData') IS NOT NULL
	DROP TABLE #MetricsData;

SELECT *
INTO #MetricsData
FROM #METRICSVALUES;

-------------------------------------------------------------
-- Set script ID for UpdateMetricsValues
-------------------------------------------------------------
(SELECT 'ScriptId', '13')	-- Set script ID for UpdateMetricsValues
--#Run

-- Create a table describing new Elements.
CREATE TABLE #ELEMENTS
(
	Id Int NOT NULL IDENTITY(1,1),
	Element VARCHAR(80),
	Typename VARCHAR(30),
	Parent VARCHAR(80),
	Description VARCHAR(8000),
	Symbol VARCHAR(200)
);

-- Insert values for demonstration series
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('Scorecard for Project A', 'Scorecard', null, 'SC_DEMO_1', 'Description for Project A scorecard.');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('Project A', 'Project', 'Scorecard for Project A', 'PROJECT_DEMO_1', 'Description for Project A.');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Scorecard for Project B', 'Scorecard', null, 'SC_DEMO_2');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Project B', 'Project', 'Scorecard for Project B', 'PROJECT_DEMO_2');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Milestone A1', 'Project Milestone', 'Project A', 'MS_DEMO_1');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Milestone A2', 'Project Milestone', 'Project A', 'MS_DEMO_2');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Milestone B1', 'Project Milestone', 'Project B', 'MS_DEMO_3');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Milestone B2', 'Project Milestone', 'Project B', 'MS_DEMO_4');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol)
 VALUES ('Milestone B3', 'Project Milestone', 'Project B', 'MS_DEMO_5');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('SetBaseScorecards demo 1', 'Scorecard', null, 'SETBASESC_DEMO1', 'Basescorecard is set to SC_DEMO_1.');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('SetBaseScorecards demo 2', 'Scorecard', null, 'SETBASESC_DEMO2', 'Basescorecard is set to SC_DEMO_1.');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('SetBaseScorecards demo 3', 'Scorecard', null, 'SETBASESC_DEMO3', 'Basescorecard is set to SC_DEMO_1.');
INSERT INTO #ELEMENTS (Element, Typename, Parent, Symbol, Description)
 VALUES ('SetBaseScorecards demo 4', 'Scorecard', null, 'SETBASESC_DEMO4', 'Basescorecard is set to SC_DEMO_1.');
 
SELECT * FROM [#ELEMENTS];
(SELECT 'Title', 'NewElements') UNION ALL
(SELECT 'SheetName', 'NewElements') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport

-------------------------------------------------------------
-- Set script ID for CreateMetricsElements and call the script
-------------------------------------------------------------
-- Set values that are specific for UpdateMetricsValues
IF OBJECT_ID('tempdb..#MetricsData') IS NOT NULL
	DROP TABLE #MetricsData;

SELECT *
INTO #MetricsData
FROM #ELEMENTS;

(SELECT 'ScriptId', '18')	-- ID for CreateMetricsElements
--#Run

-- Finally, show what elements were created.
SELECT * FROM [#ElementIdMap];
(SELECT 'Title', 'Created Scorecards') UNION ALL
(SELECT 'SheetName', 'CreatedScorecards') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport

-------------------------------------------------------------
-- SetBaseScorecards demo
-------------------------------------------------------------
IF OBJECT_ID('tempdb..#SCData') IS NOT NULL
	DROP TABLE #SCData;
	
SELECT [ElementId] AS ElementId, 'SC_DEMO_1' AS BaseSCSymbol
INTO #SCData
FROM [#ElementIdMap]
WHERE [ElementName] LIKE '%SetBaseScorecards demo%'

(SELECT 'ScriptId', '19')	-- ID for SetBaseScorecards
--#Run