Page Comparison

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