CiviCRM Community Forums (archive)

yes, that is a missing feature in CiviCRM. You will need to do this via some hooks (PHP / MySQL code required)

if important to your org, consider sponsoring this development. I suspect its a 70-100 hour project.

lobo

This code is not 100% complete but you can see how I have created token blocks for last 5 pledges & last 5 contributions + a couple of others

<?php

function civitoken_civicrm_tokens( &$tokens ) {
    $tokens['civitoken'] = array( 'civitoken.contributionTotal',  'civitoken.contributionLast','civitoken.contributionList' ,'civitoken.household','civitoken.pledgeTotal' ,'civitoken.pledgeLast' ,'civitoken.pledgeList','civitoken.spouse'   );
}

function civitoken_civicrm_tokenValues( &$values, &$contactIDs, $jobID ) {

    // we could be given an array of contact IDs or a string
    require_once 'api/v2/Contact.php';
    require_once 'api/v2/Contribute.php';
    if (isset($contactIDs[0])){
    $params['contact_id'] = $contactIDs[0];
    }else{
        $params['contact_id'] = $contactIDs['contact_id'];
    }


    if ( is_array( $contactIDs ) ) {
        foreach ($contactIDs as $contactID){
          $value =& $values[$contactID];
          $value = get_contribution_details($contactID,$value);
          $value = get_spouse_details($contactID,$value);
          $value = get_pledge_details($contactID,$value);
          $value = get_household_details($contactID,$value);
          $value = get_contribution_totals($contactID,$value);
          $value = get_pledge_totals($contactID,$value);
        }

    } else {
          $value =& $values;
          $value = get_contribution_details($contactIDs,$value);
    }
   
    //  $value['civitoken.contributionTotal'] = 'total amount from above functions' ;
    }

function get_contribution_totals($cid, &$value){
    $query = "
SELECT sum( total_amount ) as total_amount,
       contact_id,
       max( receive_date ) as receive_date
FROM   civicrm_contribution
WHERE  contact_id = ( $cid )
AND    is_test = 0
GROUP BY contact_id
";

    $dao = CRM_Core_DAO::executeQuery( $query );
    while ( $dao->fetch( ) ) {
        $value['civitoken.contributionTotal'] = $dao->total_amount;
        $value['civitoken.contributionLast'  ] =  CRM_Utils_Date::customFormat($dao->receive_date, null,array('M','d','Y')) ;
    }
    return $value;

}



function get_contribution_details($cid, &$value){
   
    require_once 'api/v2/Contribute.php';
    $params['sort']       = 'receive_date DESC';
    $params['contact_id'] = $cid;
    $params['limit'] = 5;
    $contributions = civicrm_contribution_search($params);
    //get field labels

    $i = 0;
    $value['civitoken.contributionList'] = "<table><tr><th>Amount</th><th>Date</th><th>Status</th><th>Type</th><th>Worker</th><th>Promo</th><th>Dept</th><th>Staff member</th></tr>";

    foreach($contributions as $contribution){
    if ($i<5){
      $value['civitoken.contributionList'] .= "<tr><td>$" . $contribution['total_amount'] . "</td>";
      $value['civitoken.contributionList'] .= "<td>" . CRM_Utils_Date::customFormat($contribution['receive_date'], null,array('M','d','Y')) . "</td>";
      $value['civitoken.contributionList'] .= "<td>" .$contribution['contribution_status_id']. "</td>";
      $value['civitoken.contributionList'] .= "<td>" .$contribution['contribution_type']. "</td>";   
      $value['civitoken.contributionList'] .= "<td>" .$contribution['custom_50']. "</td>";   
      $value['civitoken.contributionList'] .= "<td>" .$contribution['custom_20']. "</td>";
      $value['civitoken.contributionList'] .= "<td>" .$contribution['custom_21']. "</td></tr>";
     
    }
    $i++;
    }
        $value['civitoken.contributionList'] .= "</table>";

    return $value;

}

function get_pledge_details($cid, &$value){

    require_once 'api/v2/Pledge.php';
    $params['sort']       = 'start_date DESC';
    $params['contact_id'] = $cid;
    $params['limit'] = 5;
    $params['return.custom_48'] = 1;
    $params['return.custom_185'] = 1;
    $params['return.custom_60'] = 1;
    $params['return.custom_51'] = 1;
    $params['return.pledge_amount'] = 1; 
    $params['return.pledge_status'] = 1;
    $params['return.pledge_create_date'] = 1;   
    $pledges = civicrm_pledge_get($params);

    $i = 0;
    $value['civitoken.pledgeList'] = "<table>";
    $value['civitoken.pledgeList'] .="<tr><th>Amount</th><th>Start Date</th><th>Status</th><th>Organization</th><th>Dept</th><th>Promo</th></tr>";
    foreach($pledges as $pledge){
    if ($i<5){
      $value['civitoken.pledgeList'] .= "<tr><td>$" . $pledge['pledge_amount'] . "</td>";
      $value['civitoken.pledgeList'] .= "<td>" . CRM_Utils_Date::customFormat($pledge['pledge_create_date'], null,array('M','d','Y')) . "</td>";
      $value['civitoken.pledgeList'] .= "<td>" .$pledge['pledge_status']. "</td>";
      $value['civitoken.pledgeList'] .= "<td>" .$pledge['custom_185']. "</td>";
      $value['civitoken.pledgeList'] .= "<td>" .$pledge['custom_48']. "</td>";
      $value['civitoken.pledgeList'] .= "<td>" .$pledge['custom_60']. "</td>";
      $value['civitoken.pledgeList'] .= "<td>" .$pledge['custom_51']. "</td>";
      $value['civitoken.pledgeList'] .="</tr>";             
    }
    $i++;
    }
        $value['civitoken.pledgeList'] .= "</table>";


    return $value;

}



function get_household_details($cid, &$value){
  require_once 'api/v2/Relationship.php';
  require_once 'api/v2/Contact.php';
  $params['contact_id'] = $cid;
  $params['civicrm_relationship_type_id'] = 7;
  // get relationships of type 'is household member' related to main contact
  $rels =  civicrm_contact_relationship_get($params);

  if (is_array($rels['result'])){
  // for each relationship (should only be one) get household record
  foreach ($rels['result'] as $relationship){
    if ( $relationship['civicrm_relationship_type_id'] == 7){
      $related['contact_id'] = $relationship['cid'] ;
      $related = civicrm_contact_get($related);
      $value['civitoken.household'] = "<p>".$relationship['display_name']."</p>";
      $params['contact_id'] = $relationship['cid'];
      $householdrels = civicrm_contact_relationship_get($params);
      // for each household record get all members who are not main contact ID
      foreach ($householdrels['result'] as $householdmember){
       if ($householdmember ['cid'] != $cid){
          $value['civitoken.household'] .= "<p>".$householdmember['display_name']."</p>";
        }
        }
       
       
     
    }
  }
  }
  return $value;
}

function get_spouse_details($cid, &$value){
  require_once 'api/v2/Relationship.php';
  require_once 'api/v2/Contact.php';
  $params['contact_id'] = $cid;
  $params['civicrm_relationship_type_id'] = 2;
  // get relationships of type 'is household member' related to main contact
  $rels =  civicrm_contact_relationship_get($params);

  if (is_array($rels)){
  // for each relationship (should only be one) get household record
  foreach ($rels['result'] as $relationship){
    if ( $relationship['civicrm_relationship_type_id'] == 2){
      $related['contact_id'] = $relationship['cid'] ;
      $related = civicrm_contact_get($related);
      $value['civitoken.spouse'] = "<p>".$relationship['display_name']."</p>";
      $params['contact_id'] = $relationship['cid'];
      $relatedcontacts = civicrm_contact_relationship_get($params);
      // for each household record get all members who are not main contact ID
      if (is_array($relatedcontacts)){
      foreach ($relatedcontacts['result'] as $relatedcontact){
       if ($relatedcontact ['cid'] != $cid){
          $value['civitoken.spouse'] .= "<p>".$relatedcontact['display_name']."</p>";
        }
        }
      }
       
     
    }
  }
  }
  return $value;
}

function get_pledge_totals($cid, &$value){
    $query = "
 SELECT sum( amount ) AS total_amount, contact_id, max( end_date ) AS last_date
FROM civicrm_pledge
WHERE contact_id = ( $cid )
AND is_test =0
GROUP BY contact_id

";

    $dao = CRM_Core_DAO::executeQuery( $query );
    while ( $dao->fetch( ) ) {
        $value['civitoken.pledgeTotal'] = $dao->total_amount;
        $value['civitoken.pledgeLast'] =  CRM_Utils_Date::customFormat($dao->last_date, null,array('M','d','Y')) ;
    }
    return $value;


}

To turn that code into a drupal module you need to save it as a file called civitoken.module in a folder called civitoken in your modules folder.

You also need a .info file called civitoken.info in the same folder with this in it. Then (hopefully) you can enable the module

; $Id$
name = civitoken
description = Various tokens for CiviCRM
core = 6.x
package = CiviCRM
version = 0.0
dependencies[] = "civicrm"

Of course you'll want to remove quite a few things in there - like the references to custom fields

Many Thanks for the contribution Eileen.

Looking at this problem again it seems to me that the most sensible way to solve this issue is to use the 'Find Members' function as this is based on membership data.
Once you find the members you have 4 options

1 Batch Update via profile
2 Delete Members
3 Export Members
4 Send Email to Contacts

What kind of effort would be required to
1. Modify 'Send Email to Contacts' to include membership data ?
2. Create a new Action,  'Print PDF for Contacts' and include membership data
3. Select contacts for 1 and 2 based on Communication preferences i.e Postal Mail or Email?

The Export members function does already export both Contact and Membership data (tokens)

Are we talking hundreds of hours of coding or what? If we can get an idea I would look at what we could do to help getting some funding to do this. I am sure that other CiviCRM users would be prepared to contribute to enhance the functionality of CiviCRM to allow the sending of emails or letters with membership information directly from CiviCRM rather than having to export the data and use external tools to achieve the desired result.
At the moment in my mind we have an Illogical  situation where you can search on membership tokens but you can't use them in the search results. I am sure that other CiviCRM users must have the same problems as we do.

Any thoughts gratefully received

David

Donald,
Sorry just realised I also posted similar thread on Feature requests.

David

Hi,

It's only available on my svn at the moment - still trying to tidy it up for inclusion in core.

https://svn.fuzion.co.nz/repos/fuzion-code/trunk/civicrm/core/CRM/api/v2/Pledge.php

I think I may have made slightly wrong steer in my tidy up efforts so the latest version might not be quite right but just looking at that