You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 72
Next »
Overview
Dashboard component offers a graphical representation of data on the page.
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>
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.
- <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.
<orientation> - is used to define the orientation of the chart's data.
<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.
<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.
<settings> - is used to defined the chart settings and should follow the below structure.
<settings>
<setting>
<id></id>
<label></label>
<classname></classname>
</setting>
</settings>
Each setting tag should consist of:
- <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>
Each summary page parameter tag should consist of:
<columns> - is used to define the columns of a summary chart type and should follow the below structure.
<columns>
<column>
<label></label>
</column>
</columns>
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 shows a dashboard component of type BAR.
Vertical Bar Chart
.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>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 Bar Chart
.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.
.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
The following example shows a dashboard component of type LINE.
Pie Chart
The following example shows a dashboard component of type PIE.
.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
Horizontal Stacked Bar Chart
Summary Chart
The following examples show a dashboard component of type SUMMARY.
