read-adgroups.groovy

@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.25')
@Grab('log4j:log4j:1.2.17')
 
import groovy.sql.Sql
import groovy.sql.GroovyRowResult
import groovy.time.TimeCategory

def start = new Date()

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/adwords3_us_dev", "root", "password", "com.mysql.jdbc.Driver")

Integer publisherId = 7971727
String retailer = 'Walmart'

List<GroovyRowResult> listOfAdGroupsToExecute = new ArrayList<GroovyRowResult>()
List<GroovyRowResult> adGroupsToProcess = new ArrayList<GroovyRowResult>()

def adGroupBrochureProductsAndLocations = sql.rows("""
        SELECT
            REPLACE(REPLACE(ADB.products, '\r\n', ','), "'", "\'") AS products,
            REPLACE(REPLACE(ADB.additional_products, '\r\n', ','), "'", "\'") AS additionalProducts,
            REPLACE(REPLACE(ADB.locations, '\r\n', ','), "'", "\'") AS locations
        FROM ad_group_brochure ADB
            LEFT JOIN brochure BR USING (brochure_id)
        WHERE (ADB.publisher_id = ${publisherId} OR BR.publisher_id = ${publisherId})           
            AND IF(ADB.start IS NULL, BR.published_from, ADB.start) <= NOW()
            AND IF(ADB.end IS NULL, BR.published_until, ADB.end) >= IF(BR.brochure_id IS NULL, NOW(), DATE_ADD(NOW(), INTERVAL 15 MINUTE))
            AND IF(BR.brochure_id IS NULL, ADB.active = '1', ADB.active = '1' AND BR.state_id = 3);
        """)
        
println "${adGroupBrochureProductsAndLocations.size()} items found"

adGroupBrochureProductsAndLocations.each { element -> 
    
    /** Enable this if you want to print all the products, additional products and locations for the publisher
    println element.products
    println element.additionalProducts
    println element.locations
    */
    
    List<GroovyRowResult> result = sql.rows("""
            SELECT ag.ad_group_id
            FROM ad_group ag
                 JOIN campaign ca ON ca.campaign_id = ag.campaign_id
            WHERE ag.publisher_id = ${publisherId} 
                && ag.campaign_id = ca.campaign_id
                && (ag.product REGEXP REPLACE(${element.products},",","|")
                    OR ag.product REGEXP REPLACE(${element.additionalProducts},",","|"))
                && ca.name REGEXP REPLACE(${element.locations},",","|")
                && ag.deprecated = '0';
                """)
                
    listOfAdGroupsToExecute.addAll(result)      
    println "ListOfAdGroupsToExecute = ${listOfAdGroupsToExecute.size()}"
}

String adGroupIds = listOfAdGroupsToExecute.unique().collect { adGroup ->
    return "'${adGroup.ad_group_id.toString()}'" 
}

//println "adGroupIds ${adGroupIds}"

//retrieve the list of AdGroups to be ACTIVATED
def adGroupsToActivate = sql.rows("""
    SELECT ag.ad_group_id AS adGroupId, ag.name AS adGroupName, ag.campaign_id AS campaignId,
          ca.mobile as isMobileEnhanced, ac.client_id AS accountClientId, 1 AS adGroupNewStatus, ag.keyword_max_cpc AS cpc
    FROM ad_group ag
        JOIN campaign ca ON ca.campaign_id = ag.campaign_id
        JOIN account ac ON ac.account_id = ca.account_id
    WHERE ag.publisher_id = ${publisherId}
        AND ag.ad_group_id IN (${adGroupIds}) 
        AND ag.deprecated = '0' 
        AND ag.status = 0;
""")

adGroupsToProcess.addAll(adGroupsToActivate)
println "adGroupsToActivate ${adGroupsToActivate.size()}"

//retrieve the list of AdGroups to be PAUSED
def adGroupsToPause = sql.rows("""
    SELECT ag.ad_group_id As adGroupId, ag.name AS adGroupName, ag.campaign_id AS campaignId,
          ca.mobile as isMobileEnhanced, ac.client_id AS accountClientId, 0 AS adGroupNewStatus, ag.keyword_max_cpc AS cpc
    FROM ad_group ag
        JOIN campaign ca ON ca.campaign_id = ag.campaign_id
        JOIN account ac ON ac.account_id = ca.account_id
    WHERE ag.publisher_id = ${publisherId}  
        AND ag.ad_group_id NOT IN (${adGroupIds}) 
        AND ag.status = 1;
""")

adGroupsToProcess.addAll(adGroupsToPause)
adGroupsToProcess = adGroupsToProcess.unique()

/* Enable if you want to print final result of adgroups
println "adGroupId \tadGroupName \t\t\tcampaignId \tisMobileEnhanced \taccountClientId \tadGroupNewStatus \tcpc"
adGroupsToProcess.each { adGroup ->
    println "${adGroup.adGroupId} \t${adGroup.adGroupName} \t\t${adGroup.campaignId} \t${adGroup.isMobileEnhanced} \t\t${adGroup.accountClientId} \t\t${adGroup.adGroupNewStatus} \t\t${adGroup.cpc}"
}
*/
def end = new Date()
def elapsed = TimeCategory.minus(end, start)

println "It took ${elapsed} to process ${adGroupsToProcess.size()} ad groups."