summaryrefslogtreecommitdiffstats
path: root/django/engagementmanager/sql-scripts
diff options
context:
space:
mode:
authorPaul McGoldrick <paul.mcgoldrick@att.com>2017-09-28 10:03:40 -0700
committerPaul McGoldrick <paul.mcgoldrick@att.com>2017-09-28 10:14:55 -0700
commitbd886d918ef2adbabd16c61fdd2e47984e21dfd7 (patch)
treed41683dffa58fd698df450d148fab3cc2521b0c5 /django/engagementmanager/sql-scripts
parent474554adad912f3edb7ddc3ad14406abb369fb3c (diff)
initial seed code commit VVP-5
Change-Id: I6560c87ef48a6d0d1fe8197c7c6439c7e6ad653f Signed-off-by: Paul McGoldrick <paul.mcgoldrick@att.com>
Diffstat (limited to 'django/engagementmanager/sql-scripts')
-rwxr-xr-xdjango/engagementmanager/sql-scripts/generate_excel_overview_sheet_procedure.sql203
1 files changed, 203 insertions, 0 deletions
diff --git a/django/engagementmanager/sql-scripts/generate_excel_overview_sheet_procedure.sql b/django/engagementmanager/sql-scripts/generate_excel_overview_sheet_procedure.sql
new file mode 100755
index 0000000..79925be
--- /dev/null
+++ b/django/engagementmanager/sql-scripts/generate_excel_overview_sheet_procedure.sql
@@ -0,0 +1,203 @@
+--
+-- ============LICENSE_START==========================================
+-- org.onap.vvp/engagementmgr
+-- ===================================================================
+-- Copyright C 2017 AT&T Intellectual Property. All rights reserved.
+-- ===================================================================
+--
+-- Unless otherwise specified, all software contained herein is licensed
+-- under the Apache License, Version 2.0 (the "License");
+-- you may not use this software except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+--
+--
+-- Unless otherwise specified, all documentation contained herein is licensed
+-- under the Creative Commons License, Attribution 4.0 Intl. (the "License");
+-- you may not use this documentation except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- https://creativecommons.org/licenses/by/4.0/
+--
+-- Unless required by applicable law or agreed to in writing, documentation
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- ============LICENSE_END============================================
+--
+-- ECOMP is a trademark and service mark of AT&T Intellectual Property.
+CREATE OR REPLACE FUNCTION generate_excel_overview_sheet(stage TEXT, keyword TEXT)
+RETURNS TABLE (name VARCHAR(200),
+ active_engagements INTEGER,
+ active_vfc_sum INTEGER,
+ intake_engagements INTEGER,
+ intake_vfc_sum INTEGER,
+ completed_engagements INTEGER,
+ completed_vfc_sum INTEGER,
+ total_engagements INTEGER,
+ total_vfc_sum INTEGER) AS $res$
+BEGIN
+ -- temp tables declarations:
+ CREATE TEMP TABLE result
+ (
+ name VARCHAR(200),
+ active_engagements INTEGER,
+ active_vfc_sum INTEGER,
+ intake_engagements INTEGER,
+ intake_vfc_sum INTEGER,
+ completed_engagements INTEGER,
+ completed_vfc_sum INTEGER,
+ total_engagements INTEGER,
+ total_vfc_sum INTEGER
+ ) ON COMMIT DROP;
+
+ CREATE TEMP TABLE filters
+ (
+ name VARCHAR(200),
+ value VARCHAR(200)
+ ) ON COMMIT DROP;
+
+ CREATE TEMP TABLE deployment_targets
+ (
+ deployment_target_uuid VARCHAR(200),
+ deployment_targets INTEGER
+ ) ON COMMIT DROP;
+
+ CREATE TEMP TABLE engagement_stages
+ (
+ engagement_stage VARCHAR(200),
+ total INTEGER
+ ) ON COMMIT DROP;
+
+ CREATE TEMP TABLE virtual_functions_componenets
+ (
+ name VARCHAR(200),
+ engagement_stage VARCHAR(200)
+ ) ON COMMIT DROP;
+
+ CREATE TEMP TABLE ecomp_releases
+ (
+ ecomp_release_uuid VARCHAR(200),
+ ecomp_release_name VARCHAR(200)
+ ) ON COMMIT DROP;
+
+ -- Handling filters
+ IF $1 = 'All' OR $1 = 'all' OR $1 IS NULL THEN stage := ''; ELSE stage := $1; END IF;
+ INSERT INTO filters VALUES('stage', stage);
+ IF $2 IS NULL THEN keyword := ''; ELSE keyword := $2; END IF;
+ INSERT INTO filters VALUES('keyword', keyword);
+
+ -- handling AIC (deployment targets) rows:
+ INSERT INTO deployment_targets
+ SELECT deployment_target_id, COUNT(deployment_target_id) AS total FROM ice_vf GROUP BY deployment_target_id;
+
+ -- Itearting throght each deployment:
+ DO $$
+ DECLARE
+ deployment record;
+ vf record;
+ ecomp record;
+ stage TEXT;
+ keyword TEXT;
+ BEGIN
+ -- Get the filters from outside:
+ SELECT value FROM filters WHERE name = 'stage' LIMIT 1 INTO stage;
+ SELECT value FROM filters WHERE name = 'keyword' LIMIT 1 INTO keyword;
+ FOR deployment IN SELECT * FROM deployment_targets LOOP
+ INSERT INTO engagement_stages
+ SELECT ice_engagement.engagement_stage, COUNT(ice_engagement.engagement_stage)
+ FROM ice_vf LEFT JOIN ice_engagement ON engagement_id = ice_engagement.uuid
+ WHERE ice_vf.deployment_target_id = deployment.deployment_target_uuid
+ AND ice_engagement.engagement_stage LIKE '%' || stage || '%' -- stage param filtering
+ AND (ice_engagement.engagement_manual_id LIKE '%' || keyword || '%' OR ice_vf.name LIKE '%' || keyword || '%') -- keyword param filtering
+ AND ice_engagement.engagement_stage IN ('Active', 'Intake', 'Completed')
+ GROUP BY ice_engagement.engagement_stage;
+
+ INSERT INTO virtual_functions_componenets
+ SELECT ice_vfc.name, ice_engagement.engagement_stage
+ FROM ice_vfc LEFT JOIN ice_vf ON ice_vfc.vf_id = ice_vf.uuid
+ LEFT JOIN ice_engagement ON ice_vf.engagement_id = ice_engagement.uuid
+ WHERE ice_vf.deployment_target_id = deployment.deployment_target_uuid
+ AND ice_engagement.engagement_stage LIKE '%' || stage || '%' -- stage param filtering
+ AND (ice_engagement.engagement_manual_id LIKE '%' || keyword || '%' OR ice_vf.name LIKE '%' || keyword || '%' OR ice_vfc.name LIKE '%' || keyword || '%') -- keyword param filtering
+ AND ice_engagement.engagement_stage IN ('Active', 'Intake', 'Completed');
+
+ --Insert the AIC row with its statistics:
+ INSERT INTO result VALUES
+ ((SELECT version FROM ice_deployment_target WHERE uuid = deployment.deployment_target_uuid LIMIT 1),
+ (SELECT total FROM engagement_stages where engagement_stage = 'Active' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Active'),
+ (SELECT total FROM engagement_stages where engagement_stage = 'Intake' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Intake'),
+ (SELECT total FROM engagement_stages where engagement_stage = 'Completed' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Completed'),
+ (SELECT SUM(total) FROM engagement_stages),
+ (SELECT COUNT(*) FROM virtual_functions_componenets)
+ );
+
+ --******************************************************************************************************
+ --Handling the ecomp release rows:
+ INSERT INTO ecomp_releases
+ SELECT DISTINCT ice_ecomp_release.uuid, ice_ecomp_release.name
+ FROM ice_vf LEFT JOIN ice_ecomp_release ON ice_ecomp_release.uuid = ice_vf.ecomp_release_id
+ WHERE ice_vf.deployment_target_id = deployment.deployment_target_uuid;
+
+ FOR ecomp IN SELECT * FROM ecomp_releases LOOP
+ --empty the temp tables:
+ DELETE FROM virtual_functions_componenets;
+ DELETE FROM engagement_stages;
+
+ INSERT INTO engagement_stages
+ SELECT ice_engagement.engagement_stage, COUNT(ice_engagement.engagement_stage)
+ FROM ice_vf LEFT JOIN ice_engagement ON engagement_id = ice_engagement.uuid
+ WHERE ice_vf.deployment_target_id = deployment.deployment_target_uuid AND ice_vf.ecomp_release_id = ecomp.ecomp_release_uuid
+ AND ice_engagement.engagement_stage LIKE '%' || stage || '%'--stage param filtering
+ AND (ice_engagement.engagement_manual_id LIKE '%' || keyword || '%' OR ice_vf.name LIKE '%' || keyword || '%') --keyword param filtering
+ AND ice_engagement.engagement_stage IN ('Active', 'Intake', 'Completed')
+ GROUP BY ice_engagement.engagement_stage;
+
+ INSERT INTO virtual_functions_componenets
+ SELECT ice_vfc.name, ice_engagement.engagement_stage
+ FROM ice_vfc LEFT JOIN ice_vf ON ice_vfc.vf_id = ice_vf.uuid
+ LEFT JOIN ice_engagement ON ice_vf.engagement_id = ice_engagement.uuid
+ WHERE ice_vf.deployment_target_id = deployment.deployment_target_uuid AND ice_vf.ecomp_release_id = ecomp.ecomp_release_uuid
+ AND ice_engagement.engagement_stage LIKE '%' || stage || '%'--stage param filtering
+ AND (ice_engagement.engagement_manual_id LIKE '%' || keyword || '%' OR ice_vf.name LIKE '%' || keyword || '%' OR ice_vfc.name LIKE '%' || keyword || '%') -- keyword param filtering
+ AND ice_engagement.engagement_stage IN ('Active', 'Intake', 'Completed');
+
+ --Insert the ecomp release row with its statistics:
+ INSERT INTO result VALUES
+ (' >>' || ecomp.ecomp_release_name,
+ (SELECT total FROM engagement_stages where engagement_stage = 'Active' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Active'),
+ (SELECT total FROM engagement_stages where engagement_stage = 'Intake' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Intake'),
+ (SELECT total FROM engagement_stages where engagement_stage = 'Completed' LIMIT 1),
+ (SELECT COUNT(*) FROM virtual_functions_componenets WHERE engagement_stage = 'Completed'),
+ (SELECT SUM(total) FROM engagement_stages),
+ (SELECT COUNT(*) FROM virtual_functions_componenets)
+ );
+ END LOOP;
+ --******************************************************************************************************
+
+ --empty the temp tables:
+ DELETE FROM virtual_functions_componenets;
+ DELETE FROM engagement_stages;
+ DELETE FROM ecomp_releases;
+ END LOOP;
+ END $$;
+
+ RETURN QUERY SELECT * FROM result;
+END;
+$res$
+LANGUAGE 'plpgsql' VOLATILE;