...
- Construct the SQL script that will be used to retrieve the results.
- Use executeUsingPreparedStatement(sqlStatement, dbName, parameters, dbType) method of SQLUtil class, to retrieve the results and put them in a variable of type com.crm.framework.util.ResultSetUtil.
- Convert com.crm.framework.util.ResultSetUtil to com.crm.process.dashboards.DashboardComponentData using DashboardComponentData class constructor.
Note:
- To add any additional summary filters to the summary page linked to the dashboard component, use addSummaryFilter method as shown in the following example.
- The method must always return a result of type com.crm.process.dashboards.DashboardComponentData
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
public DashboardComponentData loadCompletedActivitiesPerTypeAndMonth(
String dashboardID,
String dashboardComponentID,
String filterConditions,
ArrayList<Object> parameters) throws Exception {
CRMDODashboard dashboard = (CRMDODashboard)dashboardBean.load(dashboardID);
CRMDODashboardComponent dashboardComponent = (CRMDODashboardComponent)dashboardComponentBean.load(dashboard, dashboardComponentID);
String dashboardComponentSettings = "";
Set<CRMDOActivityType> activityTypes = activityDashboardXMLUtilBean.loadActivityTypesPerTypeFromXML(dashboardComponent);
if (activityTypes!=null && activityTypes.size()>0)
{
dashboardComponentSettings += "\n AND ACTIVITYTYPES.ACTTYPEID IN ( ";
Iterator<CRMDOActivityType> iter = activityTypes.iterator();
while (iter.hasNext())
{
CRMDOActivityType activityType = iter.next();
dashboardComponentSettings += "\n '" + activityType.getId() + "',";
}
dashboardComponentSettings = dashboardComponentSettings.substring(0, dashboardComponentSettings.length()-1);
dashboardComponentSettings += "\n )";
}
String sql =
"\n SELECT * FROM ( " +
"\n SELECT ACTIVITYTYPES.ACTTYPEID, " +
"\n CASE " +
"\n " + DateUtil.getMonthSQL(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE") +
"\n WHEN '1' THEN 'JAN' " +
"\n WHEN '2' THEN 'FEB' " +
"\n WHEN '3' THEN 'MAR' " +
"\n WHEN '4' THEN 'APR' " +
"\n WHEN '5' THEN 'MAY' " +
"\n WHEN '6' THEN 'JUN' " +
"\n WHEN '7' THEN 'JUL' " +
"\n WHEN '8' THEN 'AUG' " +
"\n WHEN '9' THEN 'SEP' " +
"\n WHEN '10' THEN 'OCT' " +
"\n WHEN '11' THEN 'NOV' " +
"\n WHEN '12' THEN 'DEC' " +
"\n END AS MONTH, " +
"\n COUNT(ACTIVITIES.ACTIVITYID) AS TOTAL " +
"\n FROM ACTIVITIES " +
"\n INNER JOIN ACTIVITYTYPES ON ACTIVITYTYPES.ACTTYPEID = ACTIVITIES.ACTTYPEID " +
"\n WHERE ACTIVITIES.ACTIVITYDELETED = 0 " +
"\n AND ACTIVITIES.LIFECYCLESTATE = '" + ActivityLifeCycleState.COMPLETED.toString() + "' " +
"\n " + dashboardComponentSettings +
"\n " + filterConditions +
"\n AND " + DateUtil.getSQLFirstDayOfMonthDate(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE")+" >= " + DateUtil.addMonthsSQLDate(getCRMSession().getDbtype(), DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), DateUtil.getFirstDateOfMonth(getCurrentDate())), -12) + " " +
"\n GROUP BY ACTIVITYTYPES.ACTTYPEID, " +
"\n CASE " +
"\n " + DateUtil.getMonthSQL(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE") +
"\n WHEN '1' THEN 'JAN' " +
"\n WHEN '2' THEN 'FEB' " +
"\n WHEN '3' THEN 'MAR' " +
"\n WHEN '4' THEN 'APR' " +
"\n WHEN '5' THEN 'MAY' " +
"\n WHEN '6' THEN 'JUN' " +
"\n WHEN '7' THEN 'JUL' " +
"\n WHEN '8' THEN 'AUG' " +
"\n WHEN '9' THEN 'SEP' " +
"\n WHEN '10' THEN 'OCT' " +
"\n WHEN '11' THEN 'NOV' " +
"\n WHEN '12' THEN 'DEC' " +
"\n END " +
"\n ) TEMP " +
"\n ORDER BY ( " +
getOrderByWithCurrentMonthPriority() +
"\n ), ACTTYPEID ";
ResultSetUtil data = SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype());
DashboardComponentData componentData = new DashboardComponentData(data);
String summaryFilter = "LIFECYCLESTATE_FILTER~" + ActivityLifeCycleState.COMPLETED.toString();
componentData.addSummaryFilter(summaryFilter);
return componentData;
} |
...