src/org/ohmage/query/impl/ClassQueries.java
public void detachUsersFromClassCampaigns(String classId)
		throws DataAccessException { 
				 
		// get a list of campaigns associated with this class 
		Collection<String> campaignIds = new LinkedList<String>();
		try {
			campaignIds = campaignClassQueries.getCampaignsAssociatedWithClass(classId);
		}
		catch(DataAccessException e) {
			throw new DataAccessException("Error retriving campaigns associated with " + classId, e); 
		}
		
		// check whether students are associated with these campaigns through other classes.
		// get a list of users that have only one class associated with the campaign.  	
		
		// if there is no other association, remove students default roles from campaigns. 		
		List<Object> parameters = new ArrayList<Object>(7);
		
		// Get default campaign roles associated with the class roles. 
		// The campaign_class_default_role maintains the mapping from class role to campaign roles
		// for each pair of campaign and class.
		String sqlGetDefaultCampaignRoles = 
				"SELECT ur.id " +
				"FROM campaign_class_default_role ccdr JOIN user_class_role ucr ON (ccdr.user_class_role_id = ucr.id) " +
				"  JOIN campaign_class cc on (ccdr.campaign_class_id = cc.id) " +
				"  JOIN class cl on (cc.class_id = cl.id) " +
				"  JOIN campaign cp on (cc.campaign_id = cp.id) " +
				"  JOIN user_role ur on (ccdr.user_role_id = ur.id) " +
				"WHERE cp.urn = ? " +      	// campaign_id 
				"  AND cl.urn = ? " +		// class_id 
				"  AND ucr.role = ? "; 		//user_class_role

		// get a list of users in a class with specific class role
		String sqlGetUsersWithSepcificRoleInClass =  
				"SELECT uc.user_id " +
			    "FROM user_class uc JOIN class c on (uc.class_id = c.id) " +
				" JOIN user_class_role ucr on (uc.user_class_role_id = ucr.id) " +
			    "WHERE c.urn = ? " +  	// class_id
				" AND ucr.role = ? "; 	// user_class_role
		
		// For a class role, get a list of detachable users for each campaign . 
		// A user is detachable if there is only class associated with the campaign 
		// that the user is attached to. 
		String sqlGetDetachableUsers = 
				"SELECT u.id " + 
				"FROM user u join user_class uc ON (u.id = uc.user_id) " +
				"  JOIN campaign_class cc ON (uc.class_id = cc.class_id) " +
				"  JOIN campaign cp ON (cc.campaign_id = cp.id) " +
				"WHERE cp.urn = ? " +  // campaign_id 
				"  AND u.id IN ( " + sqlGetUsersWithSepcificRoleInClass +
				" ) " +
				"GROUP BY u.id HAVING count(distinct uc.class_id) = 1";
		
		// for each campaign associated with the class
		for (String campaignId : campaignIds){
			// For each class role
			for(Clazz.Role classRole : Clazz.Role.values()) {
				// clear the parameter list
				parameters.clear();
				
				// delete the campaign roles associated with users if 
				// there is no other classes associated with this campaign and user.
				StringBuilder sqlStmt = new StringBuilder();
				sqlStmt.append(
						"DELETE FROM user_role_campaign " +
				        "WHERE campaign_id = (SELECT id FROM campaign WHERE urn = ?) ");
				parameters.add(campaignId);
				
				sqlStmt.append(
						"  AND user_role_id IN ( " + sqlGetDefaultCampaignRoles + ")" );
				parameters.add(campaignId);
				parameters.add(classId);
				parameters.add(classRole.toString());
				
				sqlStmt.append(
						"  AND user_id IN ( " + sqlGetDetachableUsers + ")" );
				parameters.add(campaignId);
				parameters.add(classId);
				parameters.add(classRole.toString());
				
				try {
					getJdbcTemplate().update(
							sqlStmt.toString(),
							parameters.toArray());
				}
				catch(org.springframework.dao.DataAccessException e) {
					throw new DataAccessException("Error executing SQL '" + sqlStmt + "' with " + parameters.size() + 
							"parameters: " + parameters.toString(), e);
				}
			}
		}			
	}
/** 
	 * Detach users from a class, if applicable, update users' roles associated with the 
	 * campaigns in this class (e.g. for all campaigns associated with this class, if users are 
	 * associated with them through this class only, then also detach users from those campaigns.)
	 *  
	 * @param usersToRemove A list of users to be detached from a class 
	 * 
	 * @param classId The class id that we want to detach users from
	 * 
	 * @param campaignIds Campaigns associated with the above classId. If campaignIds is null,
	 * 					  we will look up the list from the db. 
	 * 
	 * @param transactionManager Transaction manager to be used as this operation is a part
	 * 							 of a bigger transaction.
	 * 
	 * @param status Transaction status to be used for roll back
	 * 
	 */
Detach users from a class, if applicable, update users' roles associated with the
campaigns in this class

@param usersToRemove A list of users to be detached from a class

@param classId The class id that we want to detach users from

@param campaignIds Campaigns associated with the above classId. If campaignIds is null,
we will look up the list from the db.

@param transactionManager Transaction manager to be used as this operation is a part
of a bigger transaction.

@param status Transaction status to be used for roll back
Detach users from a class if applicable update users ' roles associated with the campaigns in this class @param usersToRemove A list of users to be detached from a class @param classId The class id that we want to detach users from @param campaignIds Campaigns associated with the above classId . If campaignIds is null we will look up the list from the db . @param transactionManager Transaction manager to be used as this operation is a part of a bigger transaction . @param status Transaction status to be used for roll back
public void detachUsersFromClassCampaigns(String classId)
		throws DataAccessException { 
		Collection<String> campaignIds = new LinkedList<String>();
		try {
			campaignIds = campaignClassQueries.getCampaignsAssociatedWithClass(classId);
		}
		catch(DataAccessException e) {
			throw new DataAccessException("Error retriving campaigns associated with " + classId, e); 
		}
		List<Object> parameters = new ArrayList<Object>(7);
		String sqlGetDefaultCampaignRoles = 
				"SELECT ur.id " +
				"FROM campaign_class_default_role ccdr JOIN user_class_role ucr ON (ccdr.user_class_role_id = ucr.id) " +
				"  JOIN campaign_class cc on (ccdr.campaign_class_id = cc.id) " +
				"  JOIN class cl on (cc.class_id = cl.id) " +
				"  JOIN campaign cp on (cc.campaign_id = cp.id) " +
				"  JOIN user_role ur on (ccdr.user_role_id = ur.id) " +
				"WHERE cp.urn = ? " +      	
				"  AND cl.urn = ? " +		
				"  AND ucr.role = ? "; 		
		String sqlGetUsersWithSepcificRoleInClass =  
				"SELECT uc.user_id " +
			    "FROM user_class uc JOIN class c on (uc.class_id = c.id) " +
				" JOIN user_class_role ucr on (uc.user_class_role_id = ucr.id) " +
			    "WHERE c.urn = ? " +  	
				" AND ucr.role = ? "; 	
		String sqlGetDetachableUsers = 
				"SELECT u.id " + 
				"FROM user u join user_class uc ON (u.id = uc.user_id) " +
				"  JOIN campaign_class cc ON (uc.class_id = cc.class_id) " +
				"  JOIN campaign cp ON (cc.campaign_id = cp.id) " +
				"WHERE cp.urn = ? " +  
				"  AND u.id IN ( " + sqlGetUsersWithSepcificRoleInClass +
				" ) " +
				"GROUP BY u.id HAVING count(distinct uc.class_id) = 1";
		for (String campaignId : campaignIds){
			for(Clazz.Role classRole : Clazz.Role.values()) {
				parameters.clear();
				StringBuilder sqlStmt = new StringBuilder();
				sqlStmt.append(
						"DELETE FROM user_role_campaign " +
				        "WHERE campaign_id = (SELECT id FROM campaign WHERE urn = ?) ");
				parameters.add(campaignId);
				sqlStmt.append(
						"  AND user_role_id IN ( " + sqlGetDefaultCampaignRoles + ")" );
				parameters.add(campaignId);
				parameters.add(classId);
				parameters.add(classRole.toString());
				sqlStmt.append(
						"  AND user_id IN ( " + sqlGetDetachableUsers + ")" );
				parameters.add(campaignId);
				parameters.add(classId);
				parameters.add(classRole.toString());
				try {
					getJdbcTemplate().update(
							sqlStmt.toString(),
							parameters.toArray());
				}
				catch(org.springframework.dao.DataAccessException e) {
					throw new DataAccessException("Error executing SQL '" + sqlStmt + "' with " + parameters.size() + 
							"parameters: " + parameters.toString(), e);
				}
			}
		}			
	}
public void detachUsersFromClassCampaigns ( String classId ) throws DataAccessException { Collection < String > campaignIds = new LinkedList < String > ( ) ; try { campaignIds = campaignClassQueries . getCampaignsAssociatedWithClass ( classId ) ; } catch ( DataAccessException e ) { throw new DataAccessException ( "Error retriving campaigns associated with " + classId , e ) ; } List < Object > parameters = new ArrayList < Object > ( 7 ) ; String sqlGetDefaultCampaignRoles = "SELECT ur.id " + "FROM campaign_class_default_role ccdr JOIN user_class_role ucr ON (ccdr.user_class_role_id = ucr.id) " + "  JOIN campaign_class cc on (ccdr.campaign_class_id = cc.id) " + "  JOIN class cl on (cc.class_id = cl.id) " + "  JOIN campaign cp on (cc.campaign_id = cp.id) " + "  JOIN user_role ur on (ccdr.user_role_id = ur.id) " + "WHERE cp.urn = ? " + "  AND cl.urn = ? " + "  AND ucr.role = ? " ; String sqlGetUsersWithSepcificRoleInClass = "SELECT uc.user_id " + "FROM user_class uc JOIN class c on (uc.class_id = c.id) " + " JOIN user_class_role ucr on (uc.user_class_role_id = ucr.id) " + "WHERE c.urn = ? " + " AND ucr.role = ? " ; String sqlGetDetachableUsers = "SELECT u.id " + "FROM user u join user_class uc ON (u.id = uc.user_id) " + "  JOIN campaign_class cc ON (uc.class_id = cc.class_id) " + "  JOIN campaign cp ON (cc.campaign_id = cp.id) " + "WHERE cp.urn = ? " + "  AND u.id IN ( " + sqlGetUsersWithSepcificRoleInClass + " ) " + "GROUP BY u.id HAVING count(distinct uc.class_id) = 1" ; for ( String campaignId : campaignIds ) { for ( Clazz . Role classRole : Clazz . Role . values ( ) ) { parameters . clear ( ) ; StringBuilder sqlStmt = new StringBuilder ( ) ; sqlStmt . append ( "DELETE FROM user_role_campaign " + "WHERE campaign_id = (SELECT id FROM campaign WHERE urn = ?) " ) ; parameters . add ( campaignId ) ; sqlStmt . append ( "  AND user_role_id IN ( " + sqlGetDefaultCampaignRoles + ")" ) ; parameters . add ( campaignId ) ; parameters . add ( classId ) ; parameters . add ( classRole . toString ( ) ) ; sqlStmt . append ( "  AND user_id IN ( " + sqlGetDetachableUsers + ")" ) ; parameters . add ( campaignId ) ; parameters . add ( classId ) ; parameters . add ( classRole . toString ( ) ) ; try { getJdbcTemplate ( ) . update ( sqlStmt . toString ( ) , parameters . toArray ( ) ) ; } catch ( org . springframework . dao . DataAccessException e ) { throw new DataAccessException ( "Error executing SQL '" + sqlStmt + "' with " + parameters . size ( ) + "parameters: " + parameters . toString ( ) , e ) ; } } } }
Detach users from a class, if applicable, update users' roles associated with the
 campaigns in this class (e.g.
Detach users from a class if applicable update users ' roles associated with the campaigns in this class ( e . g .
// get a list of campaigns associated with this class  // check whether students are associated with these campaigns through other classes. // get a list of users that have only one class associated with the campaign.  	 // if there is no other association, remove students default roles from campaigns. 		 // Get default campaign roles associated with the class roles.  // The campaign_class_default_role maintains the mapping from class role to campaign roles // for each pair of campaign and class. // campaign_id  // class_id  //user_class_role // get a list of users in a class with specific class role // class_id // user_class_role // For a class role, get a list of detachable users for each campaign .  // A user is detachable if there is only class associated with the campaign  // that the user is attached to.  // campaign_id  // for each campaign associated with the class // For each class role // clear the parameter list // delete the campaign roles associated with users if  // there is no other classes associated with this campaign and user.