Page Comparison

<?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>

/**
* 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;
}