Change Management Schedule Optimization

Adding Robot Test Scripts for Change Management Schedule Optimization

Change-Id: Id5edf9d3fb3c2390791362692c8b25f7607045c6
Issue-ID: OPTFRA-352
Signed-off-by: RamaPrasad Amaranarayana (ra5425) <ra5425@att.com>

8 files changed, 70 insertions, 0 deletions

diff --git a/cmso-robot/robot/assets/sql/InitDB.sql b/cmso-robot/robot/assets/sql/InitDB.sql
new file mode 100644
index 0000000..72667cb
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/InitDB.sql
@@ -0,0 +1,3 @@
+INSERT INTO `scheduler`.`domains` (`domain`) VALUES ('ChangeManagement');
+INSERT INTO `scheduler`.`approval_types` (`domain`, `approval_type`, `approval_count`) VALUES ('ChangeManagement', 'Tier 2', '1');
+SET @@global.time_zone='+00:00';
\ No newline at end of file
diff --git a/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql b/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql
new file mode 100644
index 0000000..3123ad7
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql
@@ -0,0 +1,3 @@
+SET SQL_SAFE_UPDATES = 0;
+update schedules set status = 'Pending Schedule' where status = 'Optimization in Progress';
+SET SQL_SAFE_UPDATES = 1;
\ No newline at end of file
diff --git a/cmso-robot/robot/assets/sql/VericaQueries.sql b/cmso-robot/robot/assets/sql/VericaQueries.sql
new file mode 100644
index 0000000..9d59f43
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/VericaQueries.sql
@@ -0,0 +1,9 @@
+ SELECT distinct vnf_name, pserver_fqdn, pserver_hostname
+            from qa0_dcaecommon_views.v_vnf_vm_pserver;
+            
+SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vce_vm_pserver where vnf_name in ('ZRDM1MMSC04')
+UNION
+SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vnf_vm_pserver where vnf_name in ('ZRDM1MMSC04')
+UNION
+SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vpe_vm_pserver where vnf_name in ('ZRDM1MMSC04')
+;
\ No newline at end of file
diff --git a/cmso-robot/robot/assets/sql/clearthetable.sql b/cmso-robot/robot/assets/sql/clearthetable.sql
new file mode 100644
index 0000000..e382222
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/clearthetable.sql
@@ -0,0 +1,11 @@
+SET SQL_SAFE_UPDATES = 0;
+delete from DOMAIN_DATA;
+delete from CHANGE_MANAGEMENT_CHANGE_WINDOWS;
+delete from CHANGE_MANAGEMENT_RELATED_ASSETS;
+delete from CHANGE_MANAGEMENT_SCHEDULES;
+delete from CHANGE_MANAGEMENT_GROUPS;
+delete from SCHEDULE_APPROVALS;
+delete from SCHEDULE_EVENTS;
+delete from SCHEDULES;
+
+SET SQL_SAFE_UPDATES = 1;
diff --git a/cmso-robot/robot/assets/sql/demo.sql b/cmso-robot/robot/assets/sql/demo.sql
new file mode 100644
index 0000000..c099283
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/demo.sql
@@ -0,0 +1,11 @@
+  SELECT DISTINCT
+    s.schedule_id, s.status, s.schedule_info, s.schedule, g.start_time,
+    d.vnf_name, d.status vnf_status, d.status_message, d.aots_change_id, d.aots_approval_status, d.mso_request_id, d.mso_status, d.aots_status, s.user_id, FROM_UNIXTIME(d.start_time/1000) as StartTime, FROM_UNIXTIME(s.create_date_time/1000) as CreateDateTime
+FROM
+    SCHEDULES s,
+    CHANGE_MANAGEMENT_GROUPS g,
+    CHANGE_MANAGEMENT_SCHEDULES d
+WHERE
+    g.schedules_id = s.id
+	AND d.change_management_groups_id = g.id
+    order by CreateDateTime desc;    
\ No newline at end of file
diff --git a/cmso-robot/robot/assets/sql/searchSQL.sql b/cmso-robot/robot/assets/sql/searchSQL.sql
new file mode 100644
index 0000000..952800c
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/searchSQL.sql
@@ -0,0 +1,20 @@
+select distinct 
+s.vnf_name as vnFName,
+s.vnf_id as vnfId,
+s.status as status,
+s.aots_change_id as aotsChangeId,
+s.start_time as startTime,
+s.finish_time as finishTime,
+g.group_id as groupId,
+g.last_instance_start_time as lastInstanceStartTime,
+g.policy_id as policyId,
+g.schedules_id as id,
+ss.schedule_id as scheduleId,
+dd.name
+from change_management_schedules s 
+inner join change_management_groups g on s.change_management_groups_id = g.id 
+inner join schedules ss on g.schedules_id = ss.id
+left outer join domain_data dd on ss.id = dd.schedules_id 
+left outer join schedule_approvals sa on ss.id = sa.schedules_id 
+inner join approval_types at on sa.approval_type_id = at.id
+left outer join change_management_related_assets cmra on cmra.change_management_schedule_id = s.id
diff --git a/cmso-robot/robot/assets/sql/selectDetails.sql b/cmso-robot/robot/assets/sql/selectDetails.sql
new file mode 100644
index 0000000..74fa390
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/selectDetails.sql
@@ -0,0 +1,4 @@
+SET @@global.time_zone='+00:00';
+select distinct ss.create_date_time, s.id as id, s.vnf_name as vnf_name, s.vnf_id as vnf_id, s.status as status, s.aots_change_id as aots_change_Id, s.start_time as start_time, s.finish_time as finish_time, g.group_id as group_id, g.last_instance_start_time as last_instance_start_time, g.policy_id as policy_id, g.schedules_id as schedules_id from change_management_schedules s inner join change_management_groups g on s.change_management_groups_id = g.id inner join schedules ss on g.schedules_id = ss.id  left outer join domain_data dd on ss.id = dd.schedules_id left outer join schedule_approvals sa on ss.id = sa.schedules_id inner join approval_types at on sa.approval_type_id = at.id left outer join change_management_related_assets cmra on cmra.change_management_schedule_id = s.id  
+-- where ((ss.create_date_time >= TIMESTAMP('2017-07-14T21:30:02.000-0000') AND ss.create_date_time <= TIMESTAMP('2017-07-14T21:30:03.000-0000')  ))
+order by id 
\ No newline at end of file
diff --git a/cmso-robot/robot/assets/sql/selectall.sql b/cmso-robot/robot/assets/sql/selectall.sql
new file mode 100644
index 0000000..ba41595
--- /dev/null
+++ b/cmso-robot/robot/assets/sql/selectall.sql
@@ -0,0 +1,9 @@
+select * from DOMAINS;
+select * from DOMAIN_DATA order by schedules_id desc;
+select * from CHANGE_MANAGEMENT_SCHEDULES order by id desc;
+select * from CHANGE_MANAGEMENT_CHANGE_WINDOWS;
+select * from CHANGE_MANAGEMENT_GROUPS;
+select * from SCHEDULES order by id desc;
+select * from APPROVAL_TYPES;
+select * from SCHEDULE_APPROVALS;
+select * from SCHEDULE_EVENTS;
\ No newline at end of file