<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="BR17B" language="groovy" pageWidth="358" pageHeight="358" whenNoDataType="AllSectionsNoDetail" columnWidth="358" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error">
    <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/summary_of_host_usage_resources_br17"/>
    <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/services/repository"/>
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="is_deleted" class="java.lang.String">
        <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression>
    </parameter>
    <parameter name="P_Period" class="java.lang.Short">
        <defaultValueExpression><![CDATA[0]]></defaultValueExpression>
    </parameter>
    <parameter name="P_Start_Date" class="java.util.Date">
        <defaultValueExpression><![CDATA["01/01/2000"]]></defaultValueExpression>
    </parameter>
    <parameter name="P_DataCenter_ID" class="java.lang.String">
        <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression>
    </parameter>
    <parameter name="P_Cluster_ID" class="java.lang.String">
        <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression>
    </parameter>
    <parameter name="table_name" class="java.lang.String">
        <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "hourly" : "daily"]]></defaultValueExpression>
    </parameter>
    <parameter name="calendar_column" class="java.lang.String">
        <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "the_datetime" : "the_date"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
      TBL_$P!{table_name}_PEAKS.host_id
      ,host_name
      ,delete_date
      ,cast(AVG(TBL_$P!{table_name}_PEAKS.cpu_peak) as int) AS avg_cpu_peak
      ,cast(AVG(TBL_$P!{table_name}_PEAKS.mem_peak) as int) AS avg_mem_peak
FROM     (
    -- Calculation of daily cpu and memory usage peaks
    SELECT    v3_2_statistics_hosts_resources_usage_$P!{table_name}.host_id,
            host_name,
            history_datetime,
            delete_date,
            MAX(coalesce(max_cpu_usage,0)) as cpu_peak,
            MAX(coalesce(max_memory_usage,0)) as mem_peak
    FROM v3_2_statistics_hosts_resources_usage_$P!{table_name}
            INNER JOIN v3_2_configuration_history_hosts
                      ON (v3_2_configuration_history_hosts.host_id = v3_2_statistics_hosts_resources_usage_$P!{table_name}.host_id)
    WHERE
        v3_2_configuration_history_hosts.cluster_id in (SELECT cluster_id
                                FROM v3_2_configuration_history_clusters
                                Where datacenter_id = cast($P{P_DataCenter_ID} as uuid))
        AND v3_2_configuration_history_hosts.cluster_id = CASE $P{P_Cluster_ID}
                                WHEN NULL THEN v3_2_configuration_history_hosts.cluster_id
                                WHEN '11111111-1111-1111-1111-111111111111' THEN v3_2_configuration_history_hosts.cluster_id
                                ELSE cast($P{P_Cluster_ID} as uuid)
                                     END
        AND history_datetime >= cast($P{P_Start_Date} as date)
        AND history_datetime <= 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 v3_2_configuration_history_hosts.history_id in (SELECT max(a.history_id)
                                FROM v3_2_configuration_history_hosts a
                                GROUP BY a.host_id)
        $P!{is_deleted}
    GROUP BY v3_2_statistics_hosts_resources_usage_$P!{table_name}.host_id, host_name, delete_date, history_datetime
    ) AS TBL_$P!{table_name}_PEAKS
GROUP BY
      TBL_$P!{table_name}_PEAKS.host_id, host_name, delete_date
Order by delete_date DESC, host_id]]>
    </queryString>
    <field name="host_id" class="java.lang.Object"/>
    <field name="host_name" class="java.lang.String"/>
    <field name="delete_date" class="java.sql.Timestamp"/>
    <field name="avg_cpu_peak" class="java.lang.Integer"/>
    <field name="avg_mem_peak" class="java.lang.Integer"/>
    <variable name="avg_cpu_peak_1" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression>
    </variable>
    <variable name="avg_mem_peak_1" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression>
    </variable>
    <variable name="avg_mem_peak_2" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression>
    </variable>
    <variable name="avg_cpu_peak_2" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression>
    </variable>
    <variable name="avg_mem_peak_3" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression>
    </variable>
    <summary>
        <band height="358" splitType="Stretch">
            <scatterChart>
                <chart isShowLegend="true" evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.ScatterChartCustomizer" renderType="draw" theme="ReportsLineBarChartTheme">
                    <reportElement x="0" y="0" width="358" height="358"/>
                    <box>
                        <pen lineWidth="1.0" lineColor="#3C617F"/>
                        <topPen lineWidth="1.0" lineColor="#3C617F"/>
                        <leftPen lineWidth="1.0" lineColor="#3C617F"/>
                        <bottomPen lineWidth="1.0" lineColor="#3C617F"/>
                        <rightPen lineWidth="1.0" lineColor="#3C617F"/>
                    </box>
                    <chartTitle color="#3A5E75">
                        <font fontName="SansSerif" isBold="true"/>
                        <titleExpression><![CDATA[$R{br17b.title}]]></titleExpression>
                    </chartTitle>
                    <chartSubtitle>
                        <font fontName="SansSerif"/>
                    </chartSubtitle>
                    <chartLegend>
                        <font fontName="SansSerif"/>
                    </chartLegend>
                    <hyperlinkParameter name="_report">
                        <hyperlinkParameterExpression><![CDATA["/Reports/Executive/summary_of_host_usage_resources_br17"]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                    <hyperlinkParameter name="P_DataCenter_ID">
                        <hyperlinkParameterExpression><![CDATA[$P{P_DataCenter_ID}]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                    <hyperlinkParameter name="P_Cluster_ID">
                        <hyperlinkParameterExpression><![CDATA[$P{P_Cluster_ID}]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                    <hyperlinkParameter name="P_Period">
                        <hyperlinkParameterExpression><![CDATA[$P{P_Period}]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                    <hyperlinkParameter name="P_Start_Date">
                        <hyperlinkParameterExpression><![CDATA[$P{P_Start_Date}]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                    <hyperlinkParameter name="is_deleted">
                        <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></hyperlinkParameterExpression>
                    </hyperlinkParameter>
                </chart>
                <xyDataset>
                    <xySeries>
                        <seriesExpression><![CDATA[$F{delete_date} == null ? $R{br17.chart.active.hosts} : $R{br17.chart.deleted.hosts}]]></seriesExpression>
                        <xValueExpression><![CDATA[$F{avg_cpu_peak}]]></xValueExpression>
                        <yValueExpression><![CDATA[$F{avg_mem_peak}]]></yValueExpression>
                        <itemHyperlink>
                            <hyperlinkTooltipExpression><![CDATA[$F{host_name} + " (" + $R{br17.chart.cpu} + ": " + $F{avg_cpu_peak}.toString() + "%, " + $R{br17.chart.memory} + ": " + $F{avg_mem_peak}.toString() + "%)"]]></hyperlinkTooltipExpression>
                        </itemHyperlink>
                    </xySeries>
                </xyDataset>
                <scatterPlot isShowLines="false" isShowShapes="true">
                    <plot/>
                    <xAxisLabelExpression><![CDATA[$R{axis.cpu.usage.percent}]]></xAxisLabelExpression>
                    <xAxisFormat>
                        <axisFormat labelColor="#666666" verticalTickLabels="false">
                            <labelFont>
                                <font fontName="SansSerif"/>
                            </labelFont>
                            <tickLabelFont>
                                <font fontName="SansSerif"/>
                            </tickLabelFont>
                        </axisFormat>
                    </xAxisFormat>
                    <yAxisLabelExpression><![CDATA[$R{axis.memory.usage.percent}]]></yAxisLabelExpression>
                    <yAxisFormat>
                        <axisFormat labelColor="#666666" verticalTickLabels="false">
                            <labelFont>
                                <font fontName="SansSerif"/>
                            </labelFont>
                            <tickLabelFont>
                                <font fontName="SansSerif"/>
                            </tickLabelFont>
                        </axisFormat>
                    </yAxisFormat>
                    <domainAxisMinValueExpression><![CDATA[0]]></domainAxisMinValueExpression>
                    <domainAxisMaxValueExpression><![CDATA[100]]></domainAxisMaxValueExpression>
                    <rangeAxisMinValueExpression><![CDATA[0]]></rangeAxisMinValueExpression>
                    <rangeAxisMaxValueExpression><![CDATA[100]]></rangeAxisMaxValueExpression>
                </scatterPlot>
            </scatterChart>
        </band>
    </summary>
</jasperReport>
