Shirly Radco has uploaded a new change for review. Change subject: reports: edited ic query-only_vms_name ......................................................................
reports: edited ic query-only_vms_name I edited the, only_vms_name, input control query, so it will be more readable and added comments. Change-Id: I3d3706c6d280a80fdd2d82d79f915dd980f9db9b Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs/P_VM_ID.xml 1 file changed, 90 insertions(+), 33 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/07/26307/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs/P_VM_ID.xml b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs/P_VM_ID.xml index 87cd0ce..1893b07 100644 --- a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs/P_VM_ID.xml +++ b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs/P_VM_ID.xml @@ -2,10 +2,11 @@ <inputControl> <folder>/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_VMs</folder> <name>P_VM_ID</name> - <version>23</version> + <version>50</version> <label>$R{ic.vm.name}</label> - <creationDate>2013-01-09T15:04:16.123+02:00</creationDate> - <updateDate>2013-01-16T15:15:53.268+02:00</updateDate> + <description></description> + <creationDate>2014-04-01T10:16:12.862+03:00</creationDate> + <updateDate>2014-04-01T17:15:56.922+03:00</updateDate> <type>4</type> <mandatory>true</mandatory> <readOnly>false</readOnly> @@ -17,38 +18,94 @@ <name>VM_Name</name> <version>1</version> <label>VM Name</label> - <creationDate>2013-01-16T15:15:53.268+02:00</creationDate> - <updateDate>2013-01-16T15:15:53.268+02:00</updateDate> + <description></description> + <creationDate>2014-04-01T17:15:56.922+03:00</creationDate> + <updateDate>2014-04-01T17:15:56.922+03: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 returns vm list, filtered by datacenter, +-- clusters and vm types chosen by the user. + +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) -$P!{Active_VMs} -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 ) +WHERE + --Here we filter by the datacenter chosen by the user + 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 ) + ) + --Here we filter by the clusters 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 + --Here we filter by the vm types 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 + -- Here we get the 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 + ) + -- Here we filter deleted vms according to what the user chose + $P!{Active_VMs} +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/26307 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I3d3706c6d280a80fdd2d82d79f915dd980f9db9b Gerrit-PatchSet: 1 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
