Author Topic: custom token values and multiple language support enabled (Read 3226 times)

josue · December 14, 2010, 08:19:07 am

hey folks,

i am trying to create custom tokens for relationship info. i am using the civitest example, which i have edited to try to grab the name and address of someone with the relationship "landlord":

Code: [Select]

function opb_custom_civicrm_tokens( &$tokens ) {
    $tokens['landlord'] = array( 'relationship.landlord', 'relationship.landlord_street_address', 'relationship.landlord_city', 'relationship.landlord_state', 'relationship.landlord_zip' );
}

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 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 );
    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['relationship.landlord'] = $dao->display_name;
        $value['relationship.landlord_street_address'  ] = $dao->street_address;
        $value['relationship.landlord_city'] = $dao->city;
        $value['relationship.landlord_state'  ] = $dao->state_name;
        $value['relationship.landlord_zip'] = $dao->postal_code;
    }
}

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?

the site is running 3.2.5. please don't tell me to upgrade first. still testing the upgrade, which has some clitches.

thanks!

Eileen · December 15, 2010, 01:50:24 am

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!

Code: [Select]

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>";
        }
        }
      }


    }
  }

xavier · December 15, 2010, 03:32:04 am

Hi,

As most (all?) of the templates in the mail use smarty, crmAPI should work (but haven't tested)

X+

Piotr Szotkowski · December 15, 2010, 05:13:11 am

Quote from: josue on December 14, 2010, 08:19:07 am

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.)

josue · December 15, 2010, 02:31:59 pm

thanks eileen. i will check out your way of doing it.

piotr, my first attempt was to do it the first way (your first option). how excellent that the goal is transparency. Yay!

but when i tried it that way i got no error but also no data. the token was printed instead of the data. i figured that meant it was not working. i will try it that way again.

paz,

--josue

josue · February 27, 2011, 11:38:53 pm

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:

Code: [Select]

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...

xavier · February 28, 2011, 12:30:53 am

Hi,

Your param is $values but you set the variable $value.

One quick one: right now, you seem to fetch all the landlords of the contact. You should fetch only the active relationships (ideally taking into account the start and end date, but probably an overkill in your case).

is_active = 1 if my memory serves me

X+

Eileen · February 28, 2011, 12:35:03 am

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

Code: [Select]

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;
}

josue · February 28, 2011, 02:14:30 pm

okay, am trying again.

sorry xavier, i changed value to values and it made no difference.

i decided to try to do it the "right" way, so i am following your lead eileen. thanks for the second push

here is what i have:

Code: [Select]

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';
    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_supervisor_details($contactID,$value);
        }

    } else {
          $value =& $values;
    }
    }

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

  if (is_array($rels['result'])){
  // for each relationship (should only be one) get supervisor record
  foreach ($rels['result'] as $relationship){
    if ( $relationship['civicrm_relationship_type_id'] == 9){
      $related['contact_id'] = $relationship['cid'] ;
      $related = civicrm_contact_get($related);
      $value['supervisor.name'] = "<p>".$relationship['display_name']."</p>";
        }
      }
    }
  return $value;
}

i decided to wait on learning how to use the Location api to pull the address until i got this part figured out.

i am back where i was before. i can use drupal_set_message(print_r($value)) and i get:

Code: [Select]

Array ( [supervisor.name] =>

Xxxxx Yyyyy

which is the name of the supervisor. BUT the token is still blank.

i feel like i am missing something fundamental. any more thoughts?

xavier · March 01, 2011, 12:48:05 pm

Code: [Select]

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+

Eileen · March 01, 2011, 03:49:06 pm

Sorry - I just went back to the code - I tried to simplify it & muffed it up. This is what it actually says. I'm pretty sure I used other code as a starting point as it doesn't look like the way I would have done it on my own.

Code: [Select]

      if ( is_array( $contactIDs ) ) {
      foreach ($contactIDs as $contactID){
          $value =& $values[$contactID];
          $value = get_location_details($contactID,$value);
          $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);

        }

josue · March 02, 2011, 11:28:19 pm

Quote

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:

Code: [Select]


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...

Eileen · March 02, 2011, 11:40:18 pm

In the bit I forgot to paste last time - see my prev post

foreach ($contactIDs as $contactID){
$value =& $values[$contactID];

josue · March 03, 2011, 05:31:42 am

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:

Code: [Select]

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!

xavier · March 04, 2011, 04:53:21 am

Quote from: josue on March 03, 2011, 05:31:42 am

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

Great!

Quote from: josue on March 03, 2011, 05:31:42 am

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

Quote from: josue on March 03, 2011, 05:31:42 am

...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

Yeap, location API v2 is a bit of a PITA.

Don't miss kyle's presentation on API v3 and API explorer, you will love it:

http://wiki.civicrm.org/confluence/display/CRM/CiviCon+2011+Conference+Agenda#CiviCon2011ConferenceAgenda-api

(beside, I told him he was going to get a standing ovation at the end if he'd agreed to present it, don't let me down, go and applaud and scream like you were a 10 years old at a Justin Bieber gig).

CiviCRM Community Forums (archive)

News:

Author Topic: custom token values and multiple language support enabled (Read 3226 times)

josue

custom token values and multiple language support enabled

Eileen

Re: custom token values and multiple language support enabled

xavier

Re: custom token values and multiple language support enabled

Piotr Szotkowski

Re: custom token values and multiple language support enabled

josue

Re: custom token values and multiple language support enabled

josue

Re: custom token values and multiple language support enabled

xavier

Re: custom token values and multiple language support enabled

Eileen

Re: custom token values and multiple language support enabled

josue

Re: custom token values and multiple language support enabled

xavier

Re: custom token values and multiple language support enabled

Eileen

Re: custom token values and multiple language support enabled

josue

Re: custom token values and multiple language support enabled

Eileen

Re: custom token values and multiple language support enabled

josue

Re: custom token values and multiple language support enabled

xavier

Re: custom token values and multiple language support enabled