Shirly Radco has uploaded a new change for review. Change subject: reports: edited ic query-all_clusters_with_hosts ......................................................................
reports: edited ic query-all_clusters_with_hosts I edited the, all_clusters_with_hosts, input control query, so it will be more readable and added comments. Change-Id: I016b6152a4cb32625020b93c5adc081372d3fe1f Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts/P_Cluster_ID.xml 1 file changed, 139 insertions(+), 46 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/44/25244/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts/P_Cluster_ID.xml b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts/P_Cluster_ID.xml index d1f015a..5d9f04a 100644 --- a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts/P_Cluster_ID.xml +++ b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts/P_Cluster_ID.xml @@ -2,10 +2,10 @@ <inputControl> <folder>/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/cluster_ic/all_clusters_with_hosts</folder> <name>P_Cluster_ID</name> - <version>28</version> + <version>25</version> <label>$R{ic.cluster}</label> - <creationDate>2013-01-09T15:03:24.902+02:00</creationDate> - <updateDate>2013-01-16T15:17:37.815+02:00</updateDate> + <creationDate>2014-03-02T14:51:09.676+02:00</creationDate> + <updateDate>2014-03-02T15:22:49.896+02:00</updateDate> <type>4</type> <mandatory>true</mandatory> <readOnly>false</readOnly> @@ -17,52 +17,145 @@ <name>Cluster</name> <version>1</version> <label>Cluster</label> - <creationDate>2013-01-16T15:17:37.815+02:00</creationDate> - <updateDate>2013-01-16T15:17:37.815+02:00</updateDate> + <creationDate>2014-03-02T15:22:49.896+02:00</creationDate> + <updateDate>2014-03-02T15:22:49.896+02:00</updateDate> <language>sql</language> - <queryString>SELECT DISTINCT cast(cluster_id as varchar), delete_date, CASE - WHEN delete_date IS NULL THEN RTrim(cast(cluster_name as varchar)) - ELSE cluster_name || ' (' || loc_removed_on.removed_on || ' ' || to_char(delete_date, $P{datetimelocalepattern}) || ')' - END as combo_name, 0 as sort + <queryString>-- all_clusters_with_hosts - Cluster. + +-- This query will return the clusters list according to the datacenter that was chosen +-- as well as the "All" option", for clusters that have hosts. + +-- The list will include deleted clusters according to what the user chose in the +-- "is_deleted" parameter. + +SELECT DISTINCT + CAST ( cluster_id AS varchar ), + delete_date, + CASE + WHEN delete_date IS NULL + THEN RTRIM ( CAST ( cluster_name AS varchar ) ) + ELSE + cluster_name + || + ' (' + || + loc_removed_on.removed_on + || + ' ' + || + to_char ( delete_date, $P{datetimelocalepattern} ) + || + ')' + END + AS combo_name, + 0 AS sort FROM v3_4_configuration_history_clusters - 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 cluster_id in (SELECT DISTINCT cluster_id - FROM v3_4_configuration_history_hosts - WHERE history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_hosts a - GROUP BY a.host_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}) - AND datacenter_id = cast($P{P_DataCenter_ID} as uuid) - AND history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_clusters a - GROUP BY a.cluster_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} + 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 cluster_id IN ( + SELECT DISTINCT cluster_id + FROM v3_4_configuration_history_hosts + WHERE history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_hosts a + GROUP BY a.host_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} +) +AND datacenter_id = CAST ( $P{P_DataCenter_ID} AS uuid ) +AND history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_clusters a + GROUP BY a.cluster_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} + UNION ALL -SELECT distinct '11111111-1111-1111-1111-111111111111', cast(null as timestamp), coalesce(enum_translator_localized.value_localized,enum_translator_default.value) as value, 1 -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) + +-- Adding to the clusters list the "All" as the first option in the select list. +SELECT DISTINCT + '11111111-1111-1111-1111-111111111111', + CAST(NULL AS timestamp), + COALESCE ( + enum_translator_localized.value_localized, + enum_translator_default.value + ) + AS value, + 1 +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 language_code = 'en_US' AND enum_translator_default.enum_type = 'REPORTS_ALL' AND enum_translator_default.enum_key = 0 -- To view, visit http://gerrit.ovirt.org/25244 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I016b6152a4cb32625020b93c5adc081372d3fe1f 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
