CiviCRM Community Forums (archive)

Hi,

This isn't really an answer to your question but it's a different approach to the same requirement - we only returned display name but easy to add more.

The code is on your server -feel free to poke around in it - module name civitoken - I will blog about it eventually to share it since Peter told Lobo I would  - thanks Peter!

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['result'])){
  // 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>";
        }
        }
      }


    }
  }

Code: [Select]

    $query = "
SELECT display_name_en_US as display_name, street_address_en_US as street_address, city_en_US as city, abbreviation as state_name, postal_code
FROM civicrm_contact JOIN civicrm_address
ON civicrm_contact.id = civicrm_address.contact_id
JOIN civicrm_state_province
ON civicrm_address.state_province_id = civicrm_state_province.id
WHERE civicrm_contact.id IN
(SELECT contact_id_a as contact_id
FROM    civicrm_relationship
WHERE   contact_id_b IN ( $contactIDString )
AND     relationship_type_id = '11')
";

    $dao = CRM_Core_DAO::executeQuery( $query );

the above query works in phpmyadmin, but when i try to use the above code in a drupal module i get:

Code: [Select]

Database Error Code: Unknown column 'display_name_en_US' in 'field list', 1054

any thoughts on what is going on, what i am doing wrong?

You’re running a multilingual site and are trying to access the actual civicrm_contact table (and its *_en_US columns) rather than the non-locale-extended columns in the civicrm_contact_en_US view.

We want the multilingualisation to be transparent (so the querying code needs not to know it works on a multilingual db), so CRM_Core_DAO::executeQuery() rewrites the query that accesses civicrm_contact to work on civicrm_contact_en_US, where columns like display_name_en_US as visible under their original names (display_name in this case).

You have two options: (a) rewrite your query to be mutlilingualisation-unaware (i.e., drop *_en_US suffixes from everything and let CRM_Core_DAO::executeQuery() sort it out for you) or (b) tell CRM_Core_DAO::executeQuery() that you know what you’re doing and you don’t want to do any mutlilingualisation-related rewriting by setting its sixth param to false: CRM_Core_DAO::executeQuery($query, array(), true, null, false, false).

(The first option is recommended, of course.)

okay, i am back at this. got some help from people smarter than me. and it is still not working

here is what the module looks like:
function opb_custom_civicrm_tokens( &$tokens ) {
    $tokens['landlord'] = array(
                                'landlord.name' => 'Landlord Name',
                                'landlord.street_address' => 'Landlord Address',
                                'landlord.city' => 'Landlord City',
                                'landlord.state' => 'Landlord State',
                                'landlord.zip' => 'Landlord Zipcode'
                               );
}

function opb_custom_civicrm_tokenValues( &$values, &$contactIDs ) {
    if ( is_array( $contactIDs ) ) {
        $contactIDString = implode( ',', array_values( $contactIDs ) );
        $single = false;
    } else {
        $contactIDString = "( $contactIDs )";
        $single = true;
    }
    $query = "
        SELECT
        cc.display_name,
        ca.street_address,
        ca.city,
        csp.abbreviation,
        ca.postal_code

        FROM civicrm_contact cc

        JOIN civicrm_address ca
        ON cc.id = ca.contact_id

        JOIN civicrm_state_province csp
        ON ca.state_province_id = csp.id

        WHERE cc.id IN
        (
                SELECT contact_id_a as contact_id
                FROM    civicrm_relationship
                WHERE   contact_id_b IN ( $contactIDString )
                AND     relationship_type_id = '11')

        AND ca.is_primary = '1'
";
    $dao = CRM_Core_DAO::executeQuery( $query );
    while ( $dao->fetch( ) ) {
        if ( $single ) {
            $value =& $values;
        } else {
            if ( ! array_key_exists( $dao->contact_id, $values ) ) {
                $values[$dao->contact_id] = array( );
            }

            $value =& $values[$dao->contact_id];
        }

        $value['landlord.name'          ] = $dao->display_name;
        $value['landlord.street_address'] = $dao->street_address;
        $value['landlord.city'          ] = $dao->city;
        $value['landlord.state'         ] = $dao->state_name;
        $value['landlord.zip'           ] = $dao->postal_code;
    }
}

the tokens have always shown up. when i try to use them i just get blanks. we were able to verify this evening that the data is being pulled properly into the $value['fields'] by using drupal_set_message(print_r($value))

but it does not make it into the token.

help would be much appreciated...

It's all API rather than SQL but here's my cut (spouses not landlords) - I've exclude the other functions for the other tokens

function civitoken_civicrm_tokens( &$tokens ) {
    $tokens['civitoken'] = array('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 = get_spouse_details($contactID,$value);
        }

    } else {
          $value =& $values;
    }


    }

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['result'])){
  // 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 civitoken_civicrm_tokens( &$tokens ) {
    $tokens['civitoken'] = array('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 = get_spouse_details($contactID,$value);
        }

    } else {
          $value =& $values;
    }


    }



Probably just me, but I fail to see what is done with the $value once you assign it the proper value. It isn't returned, nor is it global, nor is this a param by ref

X+

Probably just me, but I fail to see what is done with the $value once you assign it the proper value. It isn't returned, nor is it global, nor is this a param by ref

X+

no, i don't think it is just you. i am pulling the data but it is not being sent to the token. i am clearly missing something that sends the data to the token. but it does not seem to be in the civitest example code either:

function civitest_civicrm_tokens( &$tokens ) {
    $tokens['contribution'] = array( 'contribution.amount', 'contribution.date' );
}

function civitest_civicrm_tokenValues( &$values, &$contactIDs ) {
    if ( is_array( $contactIDs ) ) {
        $contactIDString = implode( ',', array_values( $contactIDs ) );
        $single = false;
    } else {
        $contactIDString = "( $contactIDs )";
        $single = true;
    }

    $query = "
SELECT sum( total_amount ) as total_amount,
       contact_id,
       max( receive_date ) as receive_date
FROM   civicrm_contribution
WHERE  contact_id IN ( $contactIDString )
AND    is_test = 0
GROUP BY contact_id
";

    $dao = CRM_Core_DAO::executeQuery( $query );
    while ( $dao->fetch( ) ) {
        if ( $single ) {
            $value =& $values;
        } else {
            if ( ! array_key_exists( $dao->contact_id, $values ) ) {
                $values[$dao->contact_id] = array( );
            }
            $value =& $values[$dao->contact_id];
        }

        $value['contribution.amount'] = $dao->total_amount;
        $value['contribution.date'  ] = $dao->receive_date;
    }
}


where does it happen in this code?

someone, please help...

okay, finally, with all of your help, SUCCESS!!!

it is so annoying when the biggest block is user error. aarrgghh!

i added the missing line (thank you eileen for your patience with me) and then turned to learning how to use the location API to pull the address. once i learned it is an array in an array (['address']['city']) then i was able to pull all the data EXCEPT the state abbreviation. finally realized that it was only giving me the state_province_id and then i could use that to call the abbreviation. here is the final code:
function opb_custom_civicrm_tokens( &$tokens ) {
    $tokens['landlord'] = array(
                                'landlord.name' => 'Landlord Name',
                                'landlord.street_address' => 'Landlord Address',
                                'landlord.city' => 'Landlord City',
                                'landlord.state' => 'Landlord State',
                                'landlord.zip' => 'Landlord Zipcode'
                               );
}

function opb_custom_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_landlord_details($contactID,$value);
        }

    } else {
          $value =& $values;
    }

    }

function get_landlord_details($cid, &$value){
  require_once 'api/v2/Relationship.php';
  require_once 'api/v2/Location.php';
  require_once 'api/v2/Contact.php';
  require_once 'CRM/Core/PseudoConstant.php';
  $params['contact_id'] = $cid;
  $params['civicrm_relationship_type_id'] = 11;
  // get relationships of type 'is landlord' related to main contact
  $rels =  civicrm_contact_relationship_get($params);

  if (is_array($rels['result'])){
  // for each relationship (should only be one) get their record
  foreach ($rels['result'] as $relationship){
    if ( $relationship['civicrm_relationship_type_id'] == 11){
      $related['contact_id'] = $relationship['cid'] ;
      $related = civicrm_contact_get($related);
      $value['landlord.name'] = "<p>".$relationship['display_name']."</p>";
//drupal_set_message(print_r($value));
      $params['contact_id'] = $relationship['cid'];
      $locations = civicrm_location_get($params);
      // for each landlord record get their primary address
        if ( !civicrm_error( $locations ) ) {
                foreach($locations as $loc) {
                                if ($loc['address']['is_primary'] == 1) {
                                        $value['landlord.street_address'] = $loc['address']['street_address'];
                                        $value['landlord.city'] = $loc['address']['city'];
                                        $value['landlord.state'] = CRM_Core_PseudoConstant::stateProvinceAbbreviation($loc['address']['state_province_id']);
                                        $value['landlord.zip'] = $loc['address']['postal_code'];
                }
            }
        }
        }
      }
    }
  return $value;
}


the early lesson, which should not get lost in all this, is that the multiple language being enabled does not matter. i started with mysql statements and ended with using the APIs but having the multiple language enabled did not mean that i had to do anything different.

thanks!