FlexibleSearch check null params in Report Definitions Hybris

Question

I want to make a Report Definitions item in backoffice that will retrieve some columns. The query needs to be like this to retrieve what I need:

SELECT {a}, {b} , {c} FROM {model} WHERE {d} IS NULL AND {e} IS NULL

All values (a,b...e) are strings. I want to parametrize the {d} and {e} column, but I want to be able to search exactly the query I write before. I start to do something like:

SELECT {a}, {b}, {c} FROM {model} WHERE {d}=?dStatus AND {e}=?eStatus

, and dStatus and eStatus paramas was java.lang.String types, but this didn't work.

It's possible to make this flexibleSearch query with {d} and {e} columns parametrized to search for null values in Report Definitions?

Answer 1

Hi If you want to make a Report Definition parameterized it's Possible.

you can do it via the following steps.

<itemtype code="testBasicOrderEntryReport" extends="Item" metatype="ViewType"
                  jaloonly="true" jaloclass="de.hybris.platform.util.ViewResultItem" generate="false">
            <description>Basic Order Entry Report</description>
            <custom-properties>
                <property name="legacyPersistence">
                    <value>java.lang.Boolean.TRUE</value>
                </property>
                <property name="query">
                    <value>
                        " SELECT "
                        + "{BaseStore.name} AS country, "
                        + "{Order.code} AS orderCode, "
                        + "DATE({Order.date}) AS orderDate, "
                        + "{Product.code} AS productCode, "
                        + "{OrderEntry.quantity} AS qty, "
                        + "{OrderMargin.margin} AS CVR "
                        + "FROM { OrderEntry "
                        + "JOIN Order ON {OrderEntry.order} = {Order.pk} "
                        + "JOIN BaseStore ON {Order.store} = {BaseStore.pk} "
                        + "JOIN Product ON {OrderEntry.product} = {Product.pk} "
                        + "LEFT JOIN OrderMargin ON {OrderEntry.margin} = {OrderMargin.pk} "
                        + "} WHERE "
                        + "{Order.store}=?baseStoreParam "
                        + "AND "
                        + "{Order.date}&gt;=?orderStartDateParam "
                        + "AND "
                        + "{Order.date}&lt;=?orderEndDateParam "
                        + "AND "
                        + "{Order.versionID} is null "
                    </value>
                </property>
            </custom-properties>
            <attributes>
                <attribute qualifier="baseStoreParam" type="BaseStore" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.TRUE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(0)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="orderStartDateParam" type="java.util.Date" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.TRUE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(1)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="orderEndDateParam" type="java.util.Date" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.TRUE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(2)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="country" type="java.lang.String" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(0)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="orderCode" type="java.lang.String" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(1)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="orderDate" type="java.lang.String" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(2)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="productCode" type="java.lang.String" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(3)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="qty" type="java.lang.Integer" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(4)</value>
                        </property>
                    </custom-properties>
                </attribute>
                <attribute qualifier="CVR" type="java.lang.Double" metatype="ViewAttributeDescriptor">
                    <persistence type="jalo"/>
                    <modifiers write="false" search="false" optional="false"/>
                    <custom-properties>
                        <property name="param">
                            <value>Boolean.FALSE</value>
                        </property>
                        <property name="position">
                            <value>Integer.valueOf(5)</value>
                        </property>
                    </custom-properties>
                </attribute>
            </attributes>
        </itemtype>

Here baseStoreParam,orderStartDateParam,orderEndDateParam are parameter.

Note: based on the expected result value and type we need to define the position and type of that attribute in the <attributes> tag.

Answer 2

You can split original query and its condition

String query = "SELECT {a}, {b}, {c} FROM {model} WHERE {d} %s AND {e} %s";
String dStatusParam = "";
String eStatusParam = "";
if (null == dStatus) {
   dStatusParam = "IS NULL";
} else {
   dStatusParam = "= '" + dStatus + "'";
}
if (null == eStatus) {
   eStatusParam = "IS NULL";
} else {
   eStatusParam = "= '" + eStatus + "'";
}
query = String.format(query, dStatusParam, eStatusParam);