- Created by Christina Georgiou (Unlicensed) , last modified on Apr 10, 2019
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 87 Next »
Overview
Dashboard component offers a graphical representation of data on the page.
Dashboard component is configured into an XML file.
Getting Started
Component XML File
Dashboard component file should follow the below structure using the defined Tags.
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title></title> <iconclass></iconclass> <ejb></ejb> <method></method> <type></type> <orientation></orientation> <xaxislabel></xaxislabel> <yaxislabel></yaxislabel> <settings> <setting> <id></id> <label></label> <classname></classname> </setting> </settings> <summarypage></summarypage> <summarypageparameters> <summarypageparameter> <name></name> <classname></classname> <field></field> <axis></axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
Tags
A dashboard component should consist of the following tags:
- <title> - is used to define a valid key defined in translation properties file. Its translation through the glossary will be used as the presentable name of the component.
<iconclass> - is used to define a font awesome icon class that will be used to render the icon of the component.
Use font awesome icon class supported by version 4.7.0.
- <ejb> - is used to define a process ejb class name in which the method that will be used to retrieved chart’s data is implemented.
- <method> - is used to define the method that will be used to retrieve chart's data.
<type> - is used to define the type of the chart.
Allowed values are BAR, DONUT, LINE, PIE, STACKED_BAR and SUMMARY.
<orientation> - is used to define the orientation of the chart's data.
Allowed values are VERTICAL and HORIZONTAL.
Applicable only for chart types BAR and STACKED_BAR.
<xaxislabel> - is used to define a valid key defined in translation properties file. Its translation through the glossary will be used as the X axis title.
Applicable only for chart types BAR, LINE and STACKED_BAR.
<yaxislabel> - is used to define a valid key defined in translation properties file. Its translation through the glossary will be used as the X axis title.
Applicable only for chart types BAR, LINE and STACKED_BAR.
<settings> - is used to defined the chart settings and should follow the below structure.
<settings> <setting> <id></id> <label></label> <classname></classname> </setting> </settings>
Settings must have the same order as the ejb method parameters.
Each setting tag should consist of:
- <id> - is used to define a unique id for the setting that will be used to construct dashboard component's xml.
- <label> - is used to define a valid key defined in translation properties file. Its translation through the glossary will be used as the setting label.
<classname> - is used to define the class name of the data object that is mapping with the setting.
- <summarypage> - is used to define the summary page path that will be opened upon clicking on chart's data.
<summarypageparameters> - is used to define the parameters that will be passed to the summary page upon clicking on chart's data and should follow the below structure.
<summarypageparameters> <summarypageparameter> <name></name> <classname></classname> <field></field> <axis></axis> </summarypageparameter> </summarypageparameters>
Summary Page Parameters must have the same order as Settings order.
Each summary page parameter tag should consist of:
- <name> - is used to define a valid summary criterion xpath of the specified summary page.
- <classname> - is used to define the class name of the data object that is mapping with the specified xpath.
<field> - is used to define the class field that will be used to load the data object.
Applicable only for CRMDO objects.
<axis> - is used to define the axis that will be used to get the parameter value.
Applicable only for chart types BAR, LINE and STACKED_BAR.
<columns> - is used to define the columns of a summary chart type and should follow the below structure.
<columns> <column> <label></label> </column> </columns>
Applicable only for chart type SUMMARY.
Columns are mapping with the chart's data. For example, if the method that retrieves chart's data returns HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> then the columns should be associated with subscription type, product and number respectively.
Each column tag should consist of:
- <label> - is used to define a valid key defined in translation properties file. Its translation through the glossary will be used as the summary column header label.
Charts
Bar Chart
The following examples show a dashboard component of type BAR.
Vertical Bar Chart
Horizontal Bar Chart
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_effective_subscriptions_per_type_and_service</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerTypeAndProduct</method> <type>BAR</type> <orientation>HORIZONTAL</orientation> <xaxislabel>key_subscription_services</xaxislabel> <yaxislabel>key_number_of_subscriptions</yaxislabel> <settings> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> <setting> <id>serviceTypes</id> <label>key_service_types</label> <classname>com.crm.dataobject.products.CRMDOProductType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>types</name> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> <field>name</field> <axis>Z_AXIS</axis> </summarypageparameter> <summarypageparameter> <name>serviceTypes</name> <classname>com.crm.dataobject.products.CRMDOProductType</classname> <field>code</field> <axis>X_AXIS</axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per type and service.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
@SuppressWarnings("unchecked")
public HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> loadSubscriptionsPerTypeAndProduct(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<CRMDOProductType> serviceTypes) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerTypeAndService(subscriptionTypes, serviceTypes);
}
HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> subscriptionsPerTypeAndService = new HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(SubscriptionLifeCycleState.EFFECTIVE.toString());
parameters.add(ProductTypeClassification.SERVICES.toString());
String sql =
"\n SELECT SUBSCRIPTIONTYPES.SUBTYPEID AS SUBSCRIPTION_TYPE, PRODUCTS.PRODID AS SERVICE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID=SUBSCRIPTIONS.SUBTYPEID " +
"\n INNER JOIN SUBSCRIPTIONSERVICES ON SUBSCRIPTIONSERVICES.SUBID=SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN PRODUCTS ON SUBSCRIPTIONSERVICES.PRODID=PRODUCTS.PRODID " +
"\n INNER JOIN PRODUCTTYPES ON PRODUCTTYPES.PRODTYPEID=PRODUCTS.PRODTYPEID " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBSCRIPTIONSERVICES.SUBSERVICEDELETED=0 " +
"\n AND PRODUCTS.PRODDELETED=0" +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE=? " +
"\n AND PRODUCTTYPES.PRODTYPECLASSIFICATION=? " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(serviceTypes!=null && serviceTypes.size()>0)
{
String serviceTypesSql = "";
Iterator<CRMDOProductType> serviceTypesIter = serviceTypes.iterator();
while(serviceTypesIter.hasNext())
{
CRMDOProductType serviceType = serviceTypesIter.next();
if(serviceType!=null)
{
parameters.add(serviceType.getId());
if(serviceTypesSql.length()>0)
{
serviceTypesSql += ",";
}
serviceTypesSql += "?";
}
}
sql += " AND PRODUCTTYPES.PRODTYPEID IN (" + serviceTypesSql + ") ";
}
sql += "\n GROUP BY SUBSCRIPTIONTYPES.SUBTYPEID, PRODUCTS.PRODID " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("SUBSCRIPTION_TYPE")!=null &&
rsu.getString("SERVICE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription type
CRMDOSubscriptionType subscriptionType = (CRMDOSubscriptionType)subscriptionTypeBean.load(rsu.getString("SUBSCRIPTION_TYPE"), new ArrayList<String>());
//load service
CRMDOProduct service = (CRMDOProduct)productBean.load(rsu.getString("SERVICE"), new ArrayList<String>());
HashMap<CRMDOProduct, Integer> perService = new HashMap<CRMDOProduct, Integer>();
if(containsKey(subscriptionsPerTypeAndService, subscriptionType))
{
perService = (HashMap<CRMDOProduct, Integer>) get(subscriptionsPerTypeAndService, subscriptionType);
perService.put(service, rsu.getInteger("TOTAL"));
}
else
{
perService.put(service, rsu.getInteger("TOTAL"));
subscriptionsPerTypeAndService.put(subscriptionType, perService);
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerTypeAndService = hook.afterLoadSubscriptionsPerTypeAndService(subscriptionsPerTypeAndService);
}
return subscriptionsPerTypeAndService;
}
Donut Chart
The following example show a dashboard component of type DONUT.
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_subscriptions_per_life_cycle_state</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerLifeCycleState</method> <type>DONUT</type> <orientation></orientation> <xaxislabel></xaxislabel> <yaxislabel></yaxislabel> <settings> <setting> <id>lifeCycleStates</id> <label>key_life_cycle_states</label> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> </setting> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>lifeCycleState</name> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> <field></field> <axis></axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per life Cycle State.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
public HashMap<SubscriptionLifeCycleState, Integer> loadSubscriptionsPerLifeCycleState(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<SubscriptionLifeCycleState> subscriptionLifeCycleStates) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerLifeCycleState(subscriptionLifeCycleStates, subscriptionTypes);
}
HashMap<SubscriptionLifeCycleState, Integer> subscriptionsPerLifeCycleState = new HashMap<SubscriptionLifeCycleState, Integer>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
String sql =
"\n SELECT SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE AS LIFE_CYCLE_STATE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID = SUBSCRIPTIONS.SUBTYPEID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(subscriptionLifeCycleStates!=null && subscriptionLifeCycleStates.size()>0)
{
String subscriptionLifeCycleStatesSql = "";
Iterator<SubscriptionLifeCycleState> subscriptionLifeCycleStatesIter = subscriptionLifeCycleStates.iterator();
while(subscriptionLifeCycleStatesIter.hasNext())
{
SubscriptionLifeCycleState subscriptionLifeCycleState = subscriptionLifeCycleStatesIter.next();
if(subscriptionLifeCycleState!=null)
{
parameters.add(subscriptionLifeCycleState.toString());
if(subscriptionLifeCycleStatesSql.length()>0)
{
subscriptionLifeCycleStatesSql += ",";
}
subscriptionLifeCycleStatesSql += "?";
}
}
sql += " AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE IN (" + subscriptionLifeCycleStatesSql + ") ";
}
sql += "\n GROUP BY SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("LIFE_CYCLE_STATE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription life cycle state
SubscriptionLifeCycleState subscriptionLifeCycleState = SubscriptionLifeCycleState.valueOf(rsu.getString("LIFE_CYCLE_STATE"));
subscriptionsPerLifeCycleState.put(subscriptionLifeCycleState, rsu.getInteger("TOTAL"));
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerLifeCycleState = hook.afterLoadSubscriptionsPerLifeCycleState(subscriptionsPerLifeCycleState);
}
return subscriptionsPerLifeCycleState;
}
Line Chart
Pie Chart
The following example shows a dashboard component of type PIE.
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_subscriptions_per_life_cycle_state</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerLifeCycleState</method> <type>PIE</type> <orientation></orientation> <xaxislabel></xaxislabel> <yaxislabel></yaxislabel> <settings> <setting> <id>lifeCycleStates</id> <label>key_life_cycle_states</label> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> </setting> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>lifeCycleState</name> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> <field></field> <axis></axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per life Cycle State.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
public HashMap<SubscriptionLifeCycleState, Integer> loadSubscriptionsPerLifeCycleState(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<SubscriptionLifeCycleState> subscriptionLifeCycleStates) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerLifeCycleState(subscriptionLifeCycleStates, subscriptionTypes);
}
HashMap<SubscriptionLifeCycleState, Integer> subscriptionsPerLifeCycleState = new HashMap<SubscriptionLifeCycleState, Integer>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
String sql =
"\n SELECT SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE AS LIFE_CYCLE_STATE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID = SUBSCRIPTIONS.SUBTYPEID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(subscriptionLifeCycleStates!=null && subscriptionLifeCycleStates.size()>0)
{
String subscriptionLifeCycleStatesSql = "";
Iterator<SubscriptionLifeCycleState> subscriptionLifeCycleStatesIter = subscriptionLifeCycleStates.iterator();
while(subscriptionLifeCycleStatesIter.hasNext())
{
SubscriptionLifeCycleState subscriptionLifeCycleState = subscriptionLifeCycleStatesIter.next();
if(subscriptionLifeCycleState!=null)
{
parameters.add(subscriptionLifeCycleState.toString());
if(subscriptionLifeCycleStatesSql.length()>0)
{
subscriptionLifeCycleStatesSql += ",";
}
subscriptionLifeCycleStatesSql += "?";
}
}
sql += " AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE IN (" + subscriptionLifeCycleStatesSql + ") ";
}
sql += "\n GROUP BY SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("LIFE_CYCLE_STATE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription life cycle state
SubscriptionLifeCycleState subscriptionLifeCycleState = SubscriptionLifeCycleState.valueOf(rsu.getString("LIFE_CYCLE_STATE"));
subscriptionsPerLifeCycleState.put(subscriptionLifeCycleState, rsu.getInteger("TOTAL"));
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerLifeCycleState = hook.afterLoadSubscriptionsPerLifeCycleState(subscriptionsPerLifeCycleState);
}
return subscriptionsPerLifeCycleState;
}
Stacked Bar Chart
The following examples show a dashboard component of type STACKED_BAR.
Vertical Stacked Bar Chart
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_effective_subscriptions_per_type_and_service</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerTypeAndProduct</method> <type>STACKED_BAR</type> <orientation>VERTICAL</orientation> <xaxislabel>key_subscription_services</xaxislabel> <yaxislabel>key_number_of_subscriptions</yaxislabel> <settings> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> <setting> <id>serviceTypes</id> <label>key_service_types</label> <classname>com.crm.dataobject.products.CRMDOProductType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>types</name> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> <field>name</field> <axis>Z_AXIS</axis> </summarypageparameter> <summarypageparameter> <name>serviceTypes</name> <classname>com.crm.dataobject.products.CRMDOProductType</classname> <field>code</field> <axis>X_AXIS</axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per type and service.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
@SuppressWarnings("unchecked")
public HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> loadSubscriptionsPerTypeAndProduct(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<CRMDOProductType> serviceTypes) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerTypeAndService(subscriptionTypes, serviceTypes);
}
HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> subscriptionsPerTypeAndService = new HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(SubscriptionLifeCycleState.EFFECTIVE.toString());
parameters.add(ProductTypeClassification.SERVICES.toString());
String sql =
"\n SELECT SUBSCRIPTIONTYPES.SUBTYPEID AS SUBSCRIPTION_TYPE, PRODUCTS.PRODID AS SERVICE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID=SUBSCRIPTIONS.SUBTYPEID " +
"\n INNER JOIN SUBSCRIPTIONSERVICES ON SUBSCRIPTIONSERVICES.SUBID=SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN PRODUCTS ON SUBSCRIPTIONSERVICES.PRODID=PRODUCTS.PRODID " +
"\n INNER JOIN PRODUCTTYPES ON PRODUCTTYPES.PRODTYPEID=PRODUCTS.PRODTYPEID " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBSCRIPTIONSERVICES.SUBSERVICEDELETED=0 " +
"\n AND PRODUCTS.PRODDELETED=0" +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE=? " +
"\n AND PRODUCTTYPES.PRODTYPECLASSIFICATION=? " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(serviceTypes!=null && serviceTypes.size()>0)
{
String serviceTypesSql = "";
Iterator<CRMDOProductType> serviceTypesIter = serviceTypes.iterator();
while(serviceTypesIter.hasNext())
{
CRMDOProductType serviceType = serviceTypesIter.next();
if(serviceType!=null)
{
parameters.add(serviceType.getId());
if(serviceTypesSql.length()>0)
{
serviceTypesSql += ",";
}
serviceTypesSql += "?";
}
}
sql += " AND PRODUCTTYPES.PRODTYPEID IN (" + serviceTypesSql + ") ";
}
sql += "\n GROUP BY SUBSCRIPTIONTYPES.SUBTYPEID, PRODUCTS.PRODID " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("SUBSCRIPTION_TYPE")!=null &&
rsu.getString("SERVICE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription type
CRMDOSubscriptionType subscriptionType = (CRMDOSubscriptionType)subscriptionTypeBean.load(rsu.getString("SUBSCRIPTION_TYPE"), new ArrayList<String>());
//load service
CRMDOProduct service = (CRMDOProduct)productBean.load(rsu.getString("SERVICE"), new ArrayList<String>());
HashMap<CRMDOProduct, Integer> perService = new HashMap<CRMDOProduct, Integer>();
if(containsKey(subscriptionsPerTypeAndService, subscriptionType))
{
perService = (HashMap<CRMDOProduct, Integer>) get(subscriptionsPerTypeAndService, subscriptionType);
perService.put(service, rsu.getInteger("TOTAL"));
}
else
{
perService.put(service, rsu.getInteger("TOTAL"));
subscriptionsPerTypeAndService.put(subscriptionType, perService);
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerTypeAndService = hook.afterLoadSubscriptionsPerTypeAndService(subscriptionsPerTypeAndService);
}
return subscriptionsPerTypeAndService;
}
Horizontal Stacked Bar Chart
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_effective_subscriptions_per_type_and_service</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerTypeAndProduct</method> <type>BAR</type> <orientation>HORIZONTAL</orientation> <xaxislabel>key_subscription_services</xaxislabel> <yaxislabel>key_number_of_subscriptions</yaxislabel> <settings> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> <setting> <id>serviceTypes</id> <label>key_service_types</label> <classname>com.crm.dataobject.products.CRMDOProductType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>types</name> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> <field>name</field> <axis>Z_AXIS</axis> </summarypageparameter> <summarypageparameter> <name>serviceTypes</name> <classname>com.crm.dataobject.products.CRMDOProductType</classname> <field>code</field> <axis>X_AXIS</axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label></label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per type and service.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
@SuppressWarnings("unchecked")
public HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> loadSubscriptionsPerTypeAndProduct(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<CRMDOProductType> serviceTypes) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerTypeAndService(subscriptionTypes, serviceTypes);
}
HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> subscriptionsPerTypeAndService = new HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(SubscriptionLifeCycleState.EFFECTIVE.toString());
parameters.add(ProductTypeClassification.SERVICES.toString());
String sql =
"\n SELECT SUBSCRIPTIONTYPES.SUBTYPEID AS SUBSCRIPTION_TYPE, PRODUCTS.PRODID AS SERVICE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID=SUBSCRIPTIONS.SUBTYPEID " +
"\n INNER JOIN SUBSCRIPTIONSERVICES ON SUBSCRIPTIONSERVICES.SUBID=SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN PRODUCTS ON SUBSCRIPTIONSERVICES.PRODID=PRODUCTS.PRODID " +
"\n INNER JOIN PRODUCTTYPES ON PRODUCTTYPES.PRODTYPEID=PRODUCTS.PRODTYPEID " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBSCRIPTIONSERVICES.SUBSERVICEDELETED=0 " +
"\n AND PRODUCTS.PRODDELETED=0" +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE=? " +
"\n AND PRODUCTTYPES.PRODTYPECLASSIFICATION=? " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(serviceTypes!=null && serviceTypes.size()>0)
{
String serviceTypesSql = "";
Iterator<CRMDOProductType> serviceTypesIter = serviceTypes.iterator();
while(serviceTypesIter.hasNext())
{
CRMDOProductType serviceType = serviceTypesIter.next();
if(serviceType!=null)
{
parameters.add(serviceType.getId());
if(serviceTypesSql.length()>0)
{
serviceTypesSql += ",";
}
serviceTypesSql += "?";
}
}
sql += " AND PRODUCTTYPES.PRODTYPEID IN (" + serviceTypesSql + ") ";
}
sql += "\n GROUP BY SUBSCRIPTIONTYPES.SUBTYPEID, PRODUCTS.PRODID " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("SUBSCRIPTION_TYPE")!=null &&
rsu.getString("SERVICE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription type
CRMDOSubscriptionType subscriptionType = (CRMDOSubscriptionType)subscriptionTypeBean.load(rsu.getString("SUBSCRIPTION_TYPE"), new ArrayList<String>());
//load service
CRMDOProduct service = (CRMDOProduct)productBean.load(rsu.getString("SERVICE"), new ArrayList<String>());
HashMap<CRMDOProduct, Integer> perService = new HashMap<CRMDOProduct, Integer>();
if(containsKey(subscriptionsPerTypeAndService, subscriptionType))
{
perService = (HashMap<CRMDOProduct, Integer>) get(subscriptionsPerTypeAndService, subscriptionType);
perService.put(service, rsu.getInteger("TOTAL"));
}
else
{
perService.put(service, rsu.getInteger("TOTAL"));
subscriptionsPerTypeAndService.put(subscriptionType, perService);
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerTypeAndService = hook.afterLoadSubscriptionsPerTypeAndService(subscriptionsPerTypeAndService);
}
return subscriptionsPerTypeAndService;
}
Summary Chart
The following examples show a dashboard component of type SUMMARY.
Simple Summary
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_subscriptions_per_life_cycle_state</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerLifeCycleState</method> <type>SUMMARY</type> <orientation></orientation> <xaxislabel></xaxislabel> <yaxislabel></yaxislabel> <settings> <setting> <id>lifeCycleStates</id> <label>key_life_cycle_states</label> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> </setting> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>lifeCycleState</name> <classname>com.crm.dataobject.subscriptions.SubscriptionLifeCycleState</classname> <field></field> <axis></axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label>key_subscription_type</label> </column> <column> <label>key_life_cycle_state</label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per life Cycle State.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
public HashMap<SubscriptionLifeCycleState, Integer> loadSubscriptionsPerLifeCycleState(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<SubscriptionLifeCycleState> subscriptionLifeCycleStates) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerLifeCycleState(subscriptionLifeCycleStates, subscriptionTypes);
}
HashMap<SubscriptionLifeCycleState, Integer> subscriptionsPerLifeCycleState = new HashMap<SubscriptionLifeCycleState, Integer>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
String sql =
"\n SELECT SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE AS LIFE_CYCLE_STATE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID = SUBSCRIPTIONS.SUBTYPEID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(subscriptionLifeCycleStates!=null && subscriptionLifeCycleStates.size()>0)
{
String subscriptionLifeCycleStatesSql = "";
Iterator<SubscriptionLifeCycleState> subscriptionLifeCycleStatesIter = subscriptionLifeCycleStates.iterator();
while(subscriptionLifeCycleStatesIter.hasNext())
{
SubscriptionLifeCycleState subscriptionLifeCycleState = subscriptionLifeCycleStatesIter.next();
if(subscriptionLifeCycleState!=null)
{
parameters.add(subscriptionLifeCycleState.toString());
if(subscriptionLifeCycleStatesSql.length()>0)
{
subscriptionLifeCycleStatesSql += ",";
}
subscriptionLifeCycleStatesSql += "?";
}
}
sql += " AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE IN (" + subscriptionLifeCycleStatesSql + ") ";
}
sql += "\n GROUP BY SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("LIFE_CYCLE_STATE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription life cycle state
SubscriptionLifeCycleState subscriptionLifeCycleState = SubscriptionLifeCycleState.valueOf(rsu.getString("LIFE_CYCLE_STATE"));
subscriptionsPerLifeCycleState.put(subscriptionLifeCycleState, rsu.getInteger("TOTAL"));
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerLifeCycleState = hook.afterLoadSubscriptionsPerLifeCycleState(subscriptionsPerLifeCycleState);
}
return subscriptionsPerLifeCycleState;
}
Advance Summary
Output
.xml
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd"> <title>key_effective_subscriptions_per_type_and_service</title> <iconclass></iconclass> <ejb>CRMProcessSubscriptionBean</ejb> <method>loadSubscriptionsPerTypeAndProduct</method> <type>SUMMARY</type> <orientation></orientation> <xaxislabel>key_subscription_services</xaxislabel> <yaxislabel>key_number_of_subscriptions</yaxislabel> <settings> <setting> <id>types</id> <label>key_subscription_types</label> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> </setting> <setting> <id>serviceTypes</id> <label>key_service_types</label> <classname>com.crm.dataobject.products.CRMDOProductType</classname> </setting> </settings> <summarypage>subscriptions/subscriptions</summarypage> <summarypageparameters> <summarypageparameter> <name>types</name> <classname>com.crm.dataobject.subscriptions.CRMDOSubscriptionType</classname> <field>name</field> <axis>Z_AXIS</axis> </summarypageparameter> <summarypageparameter> <name>serviceTypes</name> <classname>com.crm.dataobject.products.CRMDOProductType</classname> <field>code</field> <axis>X_AXIS</axis> </summarypageparameter> </summarypageparameters> <columns> <column> <label>key_subscription_type</label> </column> <column> <label>key_service_type</label> </column> <column> <label>key_number_of_subscriptions</label> </column> </columns> </dashboardcomponent>
.java
/**
* Load the subscriptions per type and service.
*
* @param subscriptionTypes - the subscription types to load the subscriptions for
* @param services - the services to load the subscriptions for
*
* @return a map of the subscriptions per type and service. The key is the subscription type. The value is a map of the subscriptions per service for the respectively type where the key is the service and the value is the number of subscriptions.
* @throws Exception
*/
@SuppressWarnings("unchecked")
public HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> loadSubscriptionsPerTypeAndProduct(ArrayList<CRMDOSubscriptionType> subscriptionTypes, ArrayList<CRMDOProductType> serviceTypes) throws Exception {
CRMProcessSubscriptionHook hook = (CRMProcessSubscriptionHook) loadHook();
if(hook!=null)
{
hook.beforeLoadSubscriptionsPerTypeAndService(subscriptionTypes, serviceTypes);
}
HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>> subscriptionsPerTypeAndService = new HashMap<CRMDOSubscriptionType, HashMap<CRMDOProduct, Integer>>();
try
{
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(SubscriptionLifeCycleState.EFFECTIVE.toString());
parameters.add(ProductTypeClassification.SERVICES.toString());
String sql =
"\n SELECT SUBSCRIPTIONTYPES.SUBTYPEID AS SUBSCRIPTION_TYPE, PRODUCTS.PRODID AS SERVICE, COUNT(SUBSCRIPTIONS.SUBID) AS TOTAL " +
"\n FROM SUBSCRIPTIONS " +
"\n INNER JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID=SUBSCRIPTIONS.SUBTYPEID " +
"\n INNER JOIN SUBSCRIPTIONSERVICES ON SUBSCRIPTIONSERVICES.SUBID=SUBSCRIPTIONS.SUBID " +
"\n INNER JOIN PRODUCTS ON SUBSCRIPTIONSERVICES.PRODID=PRODUCTS.PRODID " +
"\n INNER JOIN PRODUCTTYPES ON PRODUCTTYPES.PRODTYPEID=PRODUCTS.PRODTYPEID " +
"\n INNER JOIN SUBLIFECYCLESTATEHISTORY ON SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID " +
"\n WHERE SUBSCRIPTIONS.SUBDELETED=0 " +
"\n AND SUBSCRIPTIONSERVICES.SUBSERVICEDELETED=0 " +
"\n AND PRODUCTS.PRODDELETED=0" +
"\n AND SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED=0 " +
"\n AND SUBLIFECYCLESTATEHISTORY.LIFECYCLESTATE=? " +
"\n AND PRODUCTTYPES.PRODTYPECLASSIFICATION=? " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE IS NOT NULL " +
"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEFROMDATE") + " <= " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) " +
"\n AND ( " +
"\n SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE IS NULL " +
"\n OR " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATETODATE") + " > " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), getCurrentDate()) + " " +
"\n ) ";
if(subscriptionTypes!=null && subscriptionTypes.size()>0)
{
String subscriptionTypesSql = "";
Iterator<CRMDOSubscriptionType> subscriptionTypesIter = subscriptionTypes.iterator();
while(subscriptionTypesIter.hasNext())
{
CRMDOSubscriptionType subscriptionType = subscriptionTypesIter.next();
if(subscriptionType!=null)
{
parameters.add(subscriptionType.getId());
if(subscriptionTypesSql.length()>0)
{
subscriptionTypesSql += ",";
}
subscriptionTypesSql += "?";
}
}
sql += " AND SUBSCRIPTIONS.SUBTYPEID IN (" + subscriptionTypesSql + ") ";
}
if(serviceTypes!=null && serviceTypes.size()>0)
{
String serviceTypesSql = "";
Iterator<CRMDOProductType> serviceTypesIter = serviceTypes.iterator();
while(serviceTypesIter.hasNext())
{
CRMDOProductType serviceType = serviceTypesIter.next();
if(serviceType!=null)
{
parameters.add(serviceType.getId());
if(serviceTypesSql.length()>0)
{
serviceTypesSql += ",";
}
serviceTypesSql += "?";
}
}
sql += " AND PRODUCTTYPES.PRODTYPEID IN (" + serviceTypesSql + ") ";
}
sql += "\n GROUP BY SUBSCRIPTIONTYPES.SUBTYPEID, PRODUCTS.PRODID " +
"\n ORDER BY COUNT(SUBSCRIPTIONS.SUBID) DESC";
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getCRMSession().getOrganisationID(), parameters);
if(rsu!=null && rsu.getRowCount()>0)
{
while (rsu.next())
{
if(rsu.getString("SUBSCRIPTION_TYPE")!=null &&
rsu.getString("SERVICE")!=null &&
rsu.getInteger("TOTAL")!=null)
{
//load subscription type
CRMDOSubscriptionType subscriptionType = (CRMDOSubscriptionType)subscriptionTypeBean.load(rsu.getString("SUBSCRIPTION_TYPE"), new ArrayList<String>());
//load service
CRMDOProduct service = (CRMDOProduct)productBean.load(rsu.getString("SERVICE"), new ArrayList<String>());
HashMap<CRMDOProduct, Integer> perService = new HashMap<CRMDOProduct, Integer>();
if(containsKey(subscriptionsPerTypeAndService, subscriptionType))
{
perService = (HashMap<CRMDOProduct, Integer>) get(subscriptionsPerTypeAndService, subscriptionType);
perService.put(service, rsu.getInteger("TOTAL"));
}
else
{
perService.put(service, rsu.getInteger("TOTAL"));
subscriptionsPerTypeAndService.put(subscriptionType, perService);
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
if(hook!=null)
{
subscriptionsPerTypeAndService = hook.afterLoadSubscriptionsPerTypeAndService(subscriptionsPerTypeAndService);
}
return subscriptionsPerTypeAndService;
}
- No labels