Shirly Radco has uploaded a new change for review.
Change subject: reports: edited ic query-vm_list
......................................................................
reports: edited ic query-vm_list
I edited the, vm_list, input control query,
so it will be more readable and added comments.
Change-Id: I8eaa8f03b6eb421e05ed4d7dde905c98fc61796f
Signed-off-by: Shirly Radco <sra...@redhat.com>
---
M
packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic/VM_List.xml
1 file changed, 105 insertions(+), 39 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/05/25305/1
diff --git
a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic/VM_List.xml
b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic/VM_List.xml
index 06f191d..affeea3 100644
---
a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic/VM_List.xml
+++
b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic/VM_List.xml
@@ -2,48 +2,114 @@
<query>
<folder>/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/vm_ic</folder>
<name>VM_List</name>
- <version>22</version>
+ <version>28</version>
<label>VM List</label>
- <creationDate>2013-01-09T15:03:40.389+02:00</creationDate>
- <updateDate>2013-01-16T15:11:23.107+02:00</updateDate>
+ <creationDate>2014-03-03T12:38:21.944+02:00</creationDate>
+ <updateDate>2014-03-04T11:09:23.345+02:00</updateDate>
<language>sql</language>
- <queryString>SELECT distinct delete_date, CASE
- WHEN delete_date IS NULL then
vm_name
- ELSE vm_name || ' (' ||
loc_removed_on.removed_on || ' ' || to_char(delete_date,
$P{datetimelocalepattern}) || ')'
- END as combo_name, cast(vm_id as
varchar)
+ <queryString>-- This query will return the vms list filtered by the chosen
+-- datacenter, cluster and vms type
+
+SELECT DISTINCT
+ delete_date,
+ CASE
+ WHEN delete_date IS NULL
+ THEN vm_name
+ ELSE
+ vm_name
+ ||
+ ' ('
+ ||
+ loc_removed_on.removed_on
+ ||
+ ' '
+ ||
+ to_char(
+ delete_date,
+ $P{datetimelocalepattern}
+ )
+ ||
+ ')'
+ END AS combo_name,
+ CAST ( vm_id AS varchar )
FROM v3_4_configuration_history_vms
- LEFT OUTER JOIN (SELECT DISTINCT
coalesce(enum_translator_localized.value_localized,enum_translator_default.value)
as removed_on
- FROM enum_translator as
enum_translator_default
- LEFT OUTER JOIN (SELECT
enum_type, enum_key, value as value_localized
-
FROM enum_translator
-
WHERE language_code = $P{userlocale}) as enum_translator_localized ON
(enum_translator_localized.enum_type = enum_translator_default.enum_type AND
enum_translator_localized.enum_key = enum_translator_default.enum_key)
- WHERE
enum_translator_default.language_code = 'en_US'
- AND
enum_translator_default.enum_type = 'REPORTS_REMOVED_ON') as loc_removed_on ON
(0=0)
-WHERE v3_4_configuration_history_vms.cluster_id in (SELECT
v3_4_configuration_history_clusters.cluster_id
- FROM
v3_4_configuration_history_clusters
- WHERE
v3_4_configuration_history_clusters.datacenter_id = cast($P{P_DataCenter_ID} as
uuid))
- AND v3_4_configuration_history_vms.cluster_id = CASE $P{P_Cluster_ID}
-
WHEN '11111111-1111-1111-1111-111111111111' then
v3_4_configuration_history_vms.cluster_id
-
ELSE cast($P{P_Cluster_ID} as uuid)
-
END
- AND v3_4_configuration_history_vms.vm_type = CASE cast($P{P_VM_Type}
as int)
-
WHEN -1 THEN v3_4_configuration_history_vms.vm_type
-
WHEN NULL THEN v3_4_configuration_history_vms.vm_type
-
ELSE cast($P{P_VM_Type} as int)
-
END
- AND history_id in (SELECT max(a.history_id)
- FROM v3_4_configuration_history_vms as a
- GROUP BY a.vm_id)
- AND create_date <= CASE
- WHEN $P{P_Period} = 0 THEN
CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day'
- WHEN $P{P_Period} = 1 THEN
CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month'
- WHEN $P{P_Period} = 2 THEN
CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month'
- WHEN $P{P_Period} = 3 THEN
CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year'
- END
- AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date}
as TIMESTAMP))
- $P!{is_deleted}
-ORDER BY delete_date DESC, combo_name
-</queryString>
+ LEFT OUTER JOIN (
+ SELECT DISTINCT
+ COALESCE (
+ enum_translator_localized.value_localized,
+ enum_translator_default.value
+ )
+ AS removed_on
+ FROM enum_translator AS enum_translator_default
+ LEFT OUTER JOIN (
+ SELECT
+ enum_type,
+ enum_key,
+ value AS value_localized
+ FROM enum_translator
+ WHERE language_code = $P{userlocale}
+ )
+ AS enum_translator_localized
+ ON (
+ enum_translator_localized.enum_type =
+ enum_translator_default.enum_type
+ AND enum_translator_localized.enum_key =
+ enum_translator_default.enum_key
+ )
+ WHERE enum_translator_default.language_code = 'en_US'
+ AND enum_translator_default.enum_type = 'REPORTS_REMOVED_ON'
+ )
+ AS loc_removed_on
+ ON ( 0 = 0 )
+-- Filter vms list according to the datacenter that was chosen by the user
+WHERE v3_4_configuration_history_vms.cluster_id IN (
+ SELECT v3_4_configuration_history_clusters.cluster_id
+ FROM v3_4_configuration_history_clusters
+ WHERE v3_4_configuration_history_clusters.datacenter_id =
+ CAST ( $P{P_DataCenter_ID} AS uuid )
+)
+-- Filter vms list according to the cluster that was chosen by the user
+AND v3_4_configuration_history_vms.cluster_id =
+ CASE $P{P_Cluster_ID}
+ WHEN '11111111-1111-1111-1111-111111111111'
+ THEN v3_4_configuration_history_vms.cluster_id
+ ELSE CAST ( $P{P_Cluster_ID} AS uuid )
+ END
+-- Filter vms list according to the vm type that was chosen by the user
+AND v3_4_configuration_history_vms.vm_type =
+ CASE CAST ( $P{P_VM_Type} AS int )
+ WHEN -1
+ THEN v3_4_configuration_history_vms.vm_type
+ WHEN NULL
+ THEN v3_4_configuration_history_vms.vm_type
+ ELSE CAST ( $P{P_VM_Type} AS int )
+ END
+-- Latest vms configuration
+AND history_id IN (
+ SELECT MAX ( a.history_id )
+ FROM v3_4_configuration_history_vms AS a
+ GROUP BY a.vm_id
+)
+AND create_date <=
+CASE
+ WHEN $P{P_Period} = 0
+ THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day'
+ WHEN $P{P_Period} = 1
+ THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month'
+ WHEN $P{P_Period} = 2
+ THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month'
+ WHEN $P{P_Period} = 3
+ THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year'
+ END
+AND (
+ delete_date IS NULL
+ OR
+ delete_date >= CAST ( $P{P_Start_Date} AS timestamp )
+)
+-- This will determine if deleted entities will be included in the report,
+-- according to the user selection for "is_deleted" parameter
+$P!{is_deleted}
+ORDER BY delete_date DESC, combo_name</queryString>
<dataSource>
<uri>/reports_resources/JDBC/data_sources/ovirt</uri>
</dataSource>
--
To view, visit http://gerrit.ovirt.org/25305
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I8eaa8f03b6eb421e05ed4d7dde905c98fc61796f
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-reports
Gerrit-Branch: master
Gerrit-Owner: Shirly Radco <sra...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
