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

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