CiviCRM Community Forums (archive)

Hi! I just wrote some hooks to add custom tokens for our website and I thought I'd share. Here is a collection of contribution-related tokens:

function hook_civicrm_tokens( &$tokens ) {
    $tokens['contribution'] = array(
        'contribution.lifetime_amount' => 'Total contributions from this contact',
        'contribution.recent_amount' => 'Amount of most recent contribution',
        'contribution.recent_date' => 'Date of most recent contribution',
        'contribution.recent_year' => 'Year of most recent contribution',
        'contribution.soft_lifetime_amount' => 'Total soft credit contributions from this contact',
        'contribution.soft_recent_amount' => 'Amount of most recent soft credit contribution',
        'contribution.soft_recent_date' => 'Date of most recent soft credit contribution',
        'contribution.soft_recent_year' => 'Year of most recent soft credit contribution',
        'contribution.hard_soft_lifetime_amount' => 'Total hard and soft contributions from this contact'
    );
}

function hook_civicrm_tokenValues( &$values, &$contact_ids ) {
    // build contact_id_string
    if(is_array($contact_ids)) {
        $contact_id_string = implode(',', array_values($contact_ids));
        $single = false;

        // add arrays for each contact, if they don't already exist
        foreach($contact_ids as $contact_id) {
            if(!array_key_exists($contact_id, $values)) {
                $values[$contact_id] = array();
            }
        }
    } else {
        $contact_id_string = "( $contact_ids )";
        $single = true;
    }

    // get lifetime amount
    $lifetime_amount = 0;
    $query = "SELECT contact_id, sum( total_amount ) as lifetime_amount FROM civicrm_contribution WHERE contact_id IN ( $contact_id_string ) AND is_test = 0 GROUP BY contact_id";
    $dao = CRM_Core_DAO::executeQuery($query);
    while($dao->fetch()) {
        $lifetime_amount =  $dao->lifetime_amount;
        if($single) {
            $values['contribution.lifetime_amount'] = $lifetime_amount;
        } else {
            $values[$dao->contact_id]['contribution.lifetime_amount'] = $lifetime_amount;
        }
    }

    // get recent amount and year
    $query = "SELECT tmp_table.contact_id, tmp_table.receive_date as recent_date, civicrm_contribution.total_amount as recent_amount FROM (SELECT contact_id, MAX( receive_date ) as receive_date FROM civicrm_contribution WHERE contact_id IN ( $contact_id_string ) AND is_test = 0 GROUP BY contact_id) as tmp_table, civicrm_contribution WHERE civicrm_contribution.contact_id = tmp_table.contact_id AND civicrm_contribution.receive_date = tmp_table.receive_date";
    $dao = CRM_Core_DAO::executeQuery($query);
    while($dao->fetch()) {
        $recent_amount = sprintf("%.2f", $dao->recent_amount);
        $timestamp = strtotime($dao->recent_date);
        $recent_date = date("F j, Y", $timestamp);
        $recent_year = date("Y", $timestamp);
        if($single) {
            $values['contribution.recent_amount'] = $recent_amount;
            $values['contribution.recent_date'] = $recent_date;
            $values['contribution.recent_year'] = $recent_year;
        } else {
            $values[$dao->contact_id]['contribution.recent_amount'] = $recent_amount;
            $values[$dao->contact_id]['contribution.recent_date'] = $recent_date;
            $values[$dao->contact_id]['contribution.recent_year'] = $recent_year;
        }
    }

    // get soft credit lifetime amount
    $soft_lifetime_amount = 0;
    $query = "SELECT contact_id, sum( amount ) as soft_lifetime_amount FROM civicrm_contribution_soft WHERE contact_id IN ( $contact_id_string ) GROUP BY contact_id";
    $dao = CRM_Core_DAO::executeQuery($query);
    while($dao->fetch()) {
        $soft_lifetime_amount = $dao->soft_lifetime_amount;
        if($single) {
            $values['contribution.soft_lifetime_amount'] = $soft_lifetime_amount;
        } else {
            $values[$dao->contact_id]['contribution.soft_lifetime_amount'] = $soft_lifetime_amount;
        }
    }

    // get soft credit recent amount and year
    $query = "SELECT tmp_table.contact_id, tmp_table.receive_date as soft_recent_date, civicrm_contribution_soft.amount as soft_recent_amount FROM (SELECT civicrm_contribution_soft.contact_id, MAX( civicrm_contribution.receive_date ) as receive_date FROM civicrm_contribution, civicrm_contribution_soft WHERE civicrm_contribution.id = civicrm_contribution_soft.contribution_id AND civicrm_contribution_soft.contact_id IN ( $contact_id_string ) AND civicrm_contribution.is_test = 0 GROUP BY civicrm_contribution_soft.contact_id) as tmp_table, civicrm_contribution, civicrm_contribution_soft WHERE civicrm_contribution.id = civicrm_contribution_soft.contribution_id AND civicrm_contribution_soft.contact_id = tmp_table.contact_id AND civicrm_contribution.receive_date = tmp_table.receive_date";
    $dao = CRM_Core_DAO::executeQuery($query);
    while($dao->fetch()) {
        $recent_amount = sprintf("%.2f", $dao->soft_recent_amount);
        $timestamp = strtotime($dao->soft_recent_date);
        $recent_date = date("F j, Y", $timestamp);
        $recent_year = date("Y", $timestamp);
        if($single) {
            $values['contribution.soft_recent_amount'] = $recent_amount;
            $values['contribution.soft_recent_date'] = $recent_date;
            $values['contribution.soft_recent_year'] = $recent_year;
        } else {
            $values[$dao->contact_id]['contribution.soft_recent_amount'] = $recent_amount;
            $values[$dao->contact_id]['contribution.soft_recent_date'] = $recent_date;
            $values[$dao->contact_id]['contribution.soft_recent_year'] = $recent_year;
        }
    }

    // hard and soft contributions
    if($single) {
        $hard_soft_lifetime_amount = $values['contribution.lifetime_amount'] + $values['contribution.soft_lifetime_amount'];
        $values['contribution.hard_soft_lifetime_amount'] = sprintf("%.2f", $hard_soft_lifetime_amount);
    } else {
        foreach($contact_ids as $contact_id) {
            $hard_soft_lifetime_amount = $values[$contact_id]['contribution.lifetime_amount'] + $values[$contact_id]['contribution.soft_lifetime_amount'];
            $values[$contact_id]['contribution.hard_soft_lifetime_amount'] = sprintf("%.2f", $hard_soft_lifetime_amount);
        }
    }
   
    // make sure everything has a value
    $default_amount = '0.00';
    $default_date = 'never';
    if($single) {
        if(!isset($values['contribution.lifetime_amount']))
            $values['contribution.lifetime_amount'] = $default_amount;
        if(!isset($values['contribution.recent_amount']))
            $values['contribution.recent_amount'] = $default_amount;
        if(!isset($values['contribution.recent_date']))
            $values['contribution.recent_date'] = $default_date;
        if(!isset($values['contribution.recent_year']))
            $values['contribution.recent_year'] = $default_date;
        if(!isset($values['contribution.soft_lifetime_amount']))
            $values['contribution.soft_lifetime_amount'] = $default_amount;
        if(!isset($values['contribution.soft_recent_amount']))
            $values['contribution.soft_recent_amount'] = $default_amount;
        if(!isset($values['contribution.soft_recent_date']))
            $values['contribution.soft_recent_date'] = $default_date;
        if(!isset($values['contribution.soft_recent_year']))
            $values['contribution.soft_recent_year'] = $default_date;
        if(!isset($values['contribution.hard_soft_lifetime_amount']))
            $values['contribution.hard_soft_lifetime_amount'] = $default_date;
    } else {
        foreach($contact_ids as $contact_id) {
            if(!isset($values[$contact_id]['contribution.lifetime_amount']))
                $values[$contact_id]['contribution.lifetime_amount'] = $default_amount;
            if(!isset($values[$contact_id]['contribution.recent_amount']))
                $values[$contact_id]['contribution.recent_amount'] = $default_amount;
            if(!isset($values[$contact_id]['contribution.recent_date']))
                $values[$contact_id]['contribution.recent_date'] = $default_date;
            if(!isset($values[$contact_id]['contribution.recent_year']))
                $values[$contact_id]['contribution.recent_year'] = $default_date;
            if(!isset($values[$contact_id]['contribution.soft_lifetime_amount']))
                $values[$contact_id]['contribution.soft_lifetime_amount'] = $default_amount;
            if(!isset($values[$contact_id]['contribution.soft_recent_amount']))
                $values[$contact_id]['contribution.soft_recent_amount'] = $default_amount;
            if(!isset($values[$contact_id]['contribution.soft_recent_date']))
                $values[$contact_id]['contribution.soft_recent_date'] = $default_date;
            if(!isset($values[$contact_id]['contribution.soft_recent_year']))
                $values[$contact_id]['contribution.soft_recent_year'] = $default_date;
            if(!isset($values[$contact_id]['contribution.hard_soft_lifetime_amount']))
                $values[$contact_id]['contribution.hard_soft_lifetime_amount'] = $default_amount;
        }
    }
}

Hi Micah, nice work!!
thanks for sharing your code, it's very useful.


My concern in this point,  if i'm not wrong, is that all those queries will be executed in any email / pdf creation, if you use any of those custom tokens or even if you don't.
So can be a very 'expensive' to process all those queries in every mailing / pdf where you don't use some/any of those tokens.
. I think you're right, it will run these queries
is there any way to avoid that?

cheers!

That's a good point, I think these queries do get run each time you send an email or generate a PDF.

But it's programmed in a such a way that there aren't any queries inside of loops, so even if you have 150,000 contacts it will only run 4 queries and each query will return 150,000 rows. So it's an extra step that slows things down, but it's not exponentially more and more expensive the more contacts you have.

It probably runs whether or not you use these tokens, but maybe CiviCRM has logic in it to only hook_civicrm_tokenValues() if you're using a token defined in hook_civicrm_tokens()? I doubt it, but that would probably be a good optimization.

Thanks for the update Lobo!
nice workaround to avoid this issue
Looking forwards anxiously to do my first installation of 4.1 when it becomes stable


cheers!