CustMatchData.java

package adwords_crm;

import com.google.api.ads.adwords.axis.factory.AdWordsServices;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterion;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionOperation;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionReturnValue;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.ApiException;
import com.google.api.ads.adwords.axis.v201609.cm.BiddableAdGroupCriterion;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionOperation;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionReturnValue;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionService;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.CriterionUserList;
import com.google.api.ads.adwords.axis.v201609.cm.Operator;
import com.google.api.ads.adwords.axis.v201609.rm.AdwordsUserListServiceInterface;
import com.google.api.ads.adwords.axis.v201609.rm.CrmBasedUserList;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperand;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperandDataType;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperation;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersReturnValue;
import com.google.api.ads.adwords.axis.v201609.rm.UserList;
import com.google.api.ads.adwords.axis.v201609.rm.UserListOperation;
import com.google.api.ads.adwords.axis.v201609.rm.UserListReturnValue;
import com.google.api.ads.adwords.lib.client.AdWordsSession;
import com.google.api.ads.common.lib.auth.OfflineCredentials;
import com.google.api.ads.common.lib.auth.OfflineCredentials.Api;
import com.google.api.ads.common.lib.conf.ConfigurationLoadException;
import com.google.api.ads.common.lib.exception.OAuthException;
import com.google.api.ads.common.lib.exception.ValidationException;
import com.google.api.client.auth.oauth2.Credential;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import com.google.common.collect.Multimap;

import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class CustMatchData {

	private static String sTDUser = "###";
	private static String sTDPassword = "####";
	private static String sTDUrl = "jdbc:teradata://acsctd2/database=####";
	private static Date date = new Date();
	private static String modifiedDate= new SimpleDateFormat("yyyy-MM-dd").format(date);
	private static Connection con;
	
	/**
	 * Creates a connection to Teradata database
	 * @return Connection con
	 */
	
	private static Connection getConnection() {
		
		 try {
	            Class.forName("com.teradata.jdbc.TeraDriver");
	            try {
	            	con = DriverManager.getConnection(sTDUrl, sTDUser, sTDPassword);

	            } catch (SQLException ex) {
	                // log an exception. from example:
	                System.out.println("Failed to create the database connection."); 
	            }
	        } catch (ClassNotFoundException ex) {
	            // log an exception. for example:
	            System.out.println("Driver not found."); 
	        }
     	return con;
	        
	    }
	
	
	/**
	 * Queries FROM MKTDEVUSERS.CSP_GOOG_CUSTMATCH_APITEST to get 
	 * emails/clientids/campaignids/filedesc to be used to upload to Adwords API
	 * @param con
	 * @return ResultSet rs
	 */
	private static ResultSet qryCustMatch(Connection con){
		ResultSet rs = null;
		String sSelAll = "SELECT FileDesc,EmailAddress256Hashed, ClientID, CampaignID FROM ####.CSP_GOOG_CUSTMATCH_APITEST ORDER BY ClientID, campaignid";
		try 
    	{
	        Statement stmt = con.createStatement();
	        stmt.setFetchSize(10000);
	        rs = stmt.executeQuery(sSelAll);
    	}
	    	catch(Exception e)
    	{
    		System.out.println(e.getMessage());
    		System.exit(0);
    	}
		//ArrayList<List<String>> test = null;
		return rs;
			
	}
	
	/**
	 * Creates a Set of Lists with ClientId and CampaignId pairings
	 * @param rs
	 * @return Set<List<String>> clientCampaignTie
	 * @throws SQLException
	 */
	private static  Set<List<String>> createClientAdGroupMap(Connection con) {
		Set<List<String>> clientCampaignTie = new HashSet<>();
		ResultSet rs = null;
		String sSelDistinct = "SELECT DISTINCT ClientID, CampaignID FROM MKTDEVUSERS.CSP_GOOG_CUSTMATCH_APITEST ORDER BY clientid, campaignid ASC";
		Statement stmt;
		
		try {
			stmt = con.createStatement();
	        rs = stmt.executeQuery(sSelDistinct);
	        
			while (rs.next()) {      
				clientCampaignTie.add(Arrays.asList(rs.getString(1), rs.getString(2)));	
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return clientCampaignTie;
	}
	
	/**
	 * Creates a Dictionary with filedesc/clientid/campaignid as the key 
	 * and a list of the hashed Emails that have the same clientid and campaignid
	 * @param rs
	 * @param clientAdMap
	 * @return
	 * @throws SQLException
	 */
	private static Multimap<String, String> emailClientAdGrpMap(ResultSet rs, Set<List<String>> clientAdMap) throws SQLException {
		Multimap<String, String> multiMap = ArrayListMultimap.create();
		
		while (rs.next()) {
			
			for (List<String> clientAd : clientAdMap){
				
				
				if (clientAd.get(0).equals(rs.getString(3)) && clientAd.get(1).equals(rs.getString(4))) {
					//System.out.println(clientAd.get(0) + " " + clientAd.get(1)+ " " + rs.getString(3) + " " + rs.getString(4));
					
					multiMap.put(rs.getString(1) + " " + rs.getString(3) + " " + rs.getString(4) , rs.getString(2));
					break;
				}
			}
		}
		return multiMap;
	}
	
	
	/**
	 * Uploads the grouped Hashed emails(Grouped via ClientId/CampaignId) to appropriate ClientId
	 * and adds the FileDesc as the name of the upload. Then returns Unique Set of List of userListId/Campaign
	 * @param emailMappings
	 * @param session
	 * @return Set<List<String>> userListIdCampId
	 */
	private static Multimap<String, String> uploadEmailsClientId(String clientId, String campaignId, 
			List<String> hashedEmails, String fileDesc, AdWordsSession session, AdWordsServices adWordsServices)  {
		//Generate a refreshable OAuth2 credential.
		Multimap<String, String> clientIduserListIdCampIdMap = ArrayListMultimap.create();
		try {

			   
			   session.setClientCustomerId(clientId);
			   //AdWordsServices adWordsServices = new AdWordsServices();
			   
			   AdwordsUserListServiceInterface userListService =
			            adWordsServices.get(session, AdwordsUserListServiceInterface.class);
			   
			   // Create a user list.
			   CrmBasedUserList userList = new CrmBasedUserList();
			   
			   userList.setName(fileDesc);
			   userList.setDescription("Uploaded via Adwords Java API");
			   
			    // See limit here: https://support.google.com/adwords/answer/6276125#requirements.
			    //userList.setMembershipLifeSpan(30L);
			   
			    // Create operation.
			    UserListOperation operation = new UserListOperation();
			    operation.setOperand(userList);
			    operation.setOperator(Operator.ADD);
			    
			    // Add user list.
			    UserListReturnValue result = userListService.mutate(new UserListOperation[] { operation });
			    
			    // Display user list.
			    UserList userListAdded = result.getValue(0);
			    System.out.printf("User list with name '%s' and ID %d was added.%n",
			            userListAdded.getName(), userListAdded.getId());
			    
			    // Get user list ID.
			    Long userListId = userListAdded.getId();
			    
			    // Create operation to add members to the user list based on email addresses.
			    MutateMembersOperation mutateMembersOperation = new MutateMembersOperation();
			    MutateMembersOperand operand = new MutateMembersOperand();
			    operand.setUserListId(userListId);
			    
			    // You can optionally provide this field.
			    operand.setDataType(MutateMembersOperandDataType.EMAIL_SHA256);
			    
			    //Convert List<String> to String[]
			    System.out.println(hashedEmails.toArray(new String[hashedEmails.size()]).length);
			    // Add email address hashes.
			    operand.setMembers(hashedEmails.toArray(new String[hashedEmails.size()]));
			    mutateMembersOperation.setOperand(operand);
			    mutateMembersOperation.setOperator(Operator.ADD);
			    
			    // Add members to the user list based on email addresses.
			    MutateMembersReturnValue mutateMembersResult =
			        userListService.mutateMembers(new MutateMembersOperation[] { mutateMembersOperation });
			    
			    //Add userListId and CampaignId to list of list so that we can add to Campaign
			    clientIduserListIdCampIdMap.put(clientId, Long.toString(userListId) + " " + campaignId);
			    //userListIdCampId.add(Arrays.asList(Long.toString(userListId), campaignId));
			    //clientCampaignTie.add(Arrays.asList(rs.getString(1), rs.getString(2)));
			    
			    
			    // Display results.
			    // Reminder: it may take several hours for the list to be populated with members.
			    //System.out.println(mutateMembersResult.getUserLists());
			    for (UserList userListResult : mutateMembersResult.getUserLists()) {
			      System.out.printf("%d email addresses were uploaded to user list with name '%s' and ID %d "
			          + "and are scheduled for review.%n",
			          hashedEmails.size(), userListResult.getName(), userListResult.getId());
			  
			    
/*			    
			    //Campaign operation 
			    CampaignCriterionServiceInterface  campaignCriterionService  =
			            adWordsServices.get(session, CampaignCriterionServiceInterface.class);
			    
			    long campId = Long.parseLong(campaignId);
			    
			    CriterionUserList userListCamp = new CriterionUserList();
			    userListCamp.setUserListId(userListId);

			    CampaignCriterion criterion = new CampaignCriterion();
			    criterion.setCampaignId(campId);
			    criterion.setCriterion(userListCamp);

			    CampaignCriterionOperation operation_camp = new CampaignCriterionOperation();
			    operation_camp.setOperand(criterion);
			    operation_camp.setOperator(Operator.ADD);

			    CampaignCriterionOperation[] operations = new CampaignCriterionOperation[] {operation_camp};
			    CampaignCriterionReturnValue result_camp = campaignCriterionService.mutate(operations);
			    
			    System.out.println("Assigned UsersList: " + userListId + " to CampaignId: " + campId);
			    */
		    }    
		 }
			catch(Exception e)
			{
	    		System.out.println(e.getMessage());
	    		System.exit(0);
			}
		
		return clientIduserListIdCampIdMap;
}
		   
	private static void assignUserListCampaign(Multimap<String, String> clientIduserListIdCampIdMap, AdWordsSession session, 
			AdWordsServices adWordsServices) throws Exception  {
		
		for (Object key : clientIduserListIdCampIdMap.keySet()) {
			String clientId = key.toString();
			
			session.setClientCustomerId(clientId);
			CampaignCriterionServiceInterface  campaignCriterionService  =
		            adWordsServices.get(session, CampaignCriterionServiceInterface.class);
			
			for (String userCampId : clientIduserListIdCampIdMap.get(clientId)) {
				String[] userCampId_list = userCampId.split(" ");
				
				long userListId = Long.parseLong(userCampId_list[0]);
				long campId = Long.parseLong(userCampId_list[1]);
				
				CriterionUserList userListCamp = new CriterionUserList();
				userListCamp.setUserListId(userListId);
				
			    CampaignCriterion criterion = new CampaignCriterion();
			    criterion.setCampaignId(campId);
			    criterion.setCriterion(userListCamp);
				
			    CampaignCriterionOperation operation_camp = new CampaignCriterionOperation();
			    operation_camp.setOperand(criterion);
			    operation_camp.setOperator(Operator.ADD);
				
			    CampaignCriterionOperation[] operations = new CampaignCriterionOperation[] {operation_camp};
			    CampaignCriterionReturnValue result_camp = campaignCriterionService.mutate(operations);
			    
			    System.out.println("Assigned UsersList: " + userListId + " to CampaignId: " + campId);
			}
		}
	}
	
	private static void runner(ResultSet rs, AdWordsSession session,
			AdWordsServices adWordsServices) throws Exception {
		
		//read First line of row to set up
		rs.next();
		
		String fileDesc = rs.getString(1);
		String clientId = rs.getString(3);
		String campaignId = rs.getString(4);
		
		List<String> hashedEmails = new ArrayList<String>();
		hashedEmails.add(rs.getString(2));
		
		int cnt = 1;
		while (rs.next()) {
		
				if (!clientId.equals(rs.getString(3)) || !campaignId.equals(rs.getString(4))) {
					System.out.println("Uploading " + hashedEmails.size() +" HashedEmail List to clientId: " + clientId + " and CampaignId: " + campaignId);
					Multimap<String, String> userListIdCampIdPair = uploadEmailsClientId(clientId, campaignId, 
							hashedEmails, fileDesc, session, adWordsServices);
					assignUserListCampaign(userListIdCampIdPair, session, adWordsServices);
					
					cnt += hashedEmails.size();
					fileDesc = rs.getString(1);
					clientId = rs.getString(3);
					campaignId = rs.getString(4);
					System.out.println("Reassigning ClientId to: " + clientId + " and CampaignId to: " + campaignId);
					
					System.out.println("Clearing List");
					hashedEmails = new ArrayList<String>();
					hashedEmails.add(rs.getString(2));
					
				}
				else {
					hashedEmails.add(rs.getString(2));
				}
			}
		
		System.out.println(cnt);
	}
  
  public static void main(String[] args) throws Exception {
	  //Use Cacertss for SSL issues
	  System.setProperty("javax.net.ssl.trustStore","C:/Users/e659383/Documents/updatecacerts");
	  System.setProperty("javax.net.ssl.trustStorePassword","changeit");
	  // Generate a refreshable OAuth2 credential.
	  Credential oAuth2Credential = new OfflineCredentials.Builder()
	    .forApi(Api.ADWORDS)
	    .fromFile()
	    .build()
	    .generateCredential();
	
	  // Construct an AdWordsSession.
	  AdWordsSession session = new AdWordsSession.Builder()
	    .fromFile()
	    .withOAuth2Credential(oAuth2Credential)
	    .build();
	  
	  // Instantiate AdWorsServices
	  AdWordsServices adWordsServices = new AdWordsServices();
	  
	  //Create Connection to TD
	  System.out.println("Creating TD Connection");
	  Connection cnxn = getConnection();
	  
	  System.out.println("Creating TD ResultSet");
	  ResultSet rs = qryCustMatch(cnxn);
	  
	  runner(rs, session, adWordsServices);
	  
	  //Uploads Grouped Hashed emails to ClientId(called UserList) returns Dictionary {Clientid : userListId campaignId}
	  //Multimap<String, String> userListIdCampIdPair = uploadEmailsClientId(uploadList, session, adWordsServices);
	  
	  //Assigns UserLists to appropriate Campaigns
	  //assignUserListCampaign(userListIdCampIdPair, session, adWordsServices);
	  
	  //System.out.println(userListIdCampIdPair);
	  
  }
}