Birthday tag script

[twowheeler]

There have been several questions in the forums from people needing to send happy birthday email from civicrm.  As a way of learning about the data model, I took a stab at it.  The following little script will tag everyone whose birthday appears to be today based on the value of the birth_date field.  I am calling it with a cron job so the tag is always up to date.   The user can just search for everyone with this tag and send them an email.  All suggestions and comments are welcome.  Extending it to send the emails automatically is left as an exercise for the reader.   :-)

Code: [Select]

<?php



// Script to find contacts with a birthday today and set a tag 'Birthday Today'

// Call this at least daily with cron so the tag is always set correctly



if (module_exists('civicrm')) {

civicrm_initialize(TRUE);

// get month and day to search on

$timearray = getdate();

$BDday =  $timearray['mday'];

$BDmon =  $timearray['mon'];



// delete old tag and create a fresh copy

$params = array( 'name' => 'Birthday Today', 'domain_id' => 1 );  

// TO DO: how do we really find the domain_id?

$oldtag = crm_get_tag($params);

if ( ! is_a( $oldtag, 'CRM_Core_Error' )) 

$out = crm_delete_tag(&$oldtag);

$tag = crm_create_tag($params);

if ( ! is_a( $tag, 'CRM_Core_Error' )) 

echo 'Tag created OK<br>';

else {

echo 'Cannot create tag! Giving up.<br>';

// print_r($tag);   // for debugging only

exit;

}



// get ids of contacts

$sql = "SELECT contact_id FROM civicrm_individual ".

"WHERE month(birth_date) = $BDmon and day(birth_date) = $BDday";

if (!$result = mysql_query( $sql )) {

echo 'No birthdays today<br>';

exit;

}



// for each id get the contact and tag it

$count = 0;

while ($params = mysql_fetch_assoc($result)) {

$contact = crm_get_contact($params);

if ( ! is_a( $contact, 'CRM_Core_Error' )) {

$entitytag = crm_create_entity_tag(&$tag, &$contact);

$count++;

} else {

echo 'Failed to tag contact id='.$params['contact_id'].'<br>';

}

}

echo "$count contacts tagged successfully<br>";

}



?>


[twowheeler]

I updated the script to also handle an additional job -- to update the value of a custom field labeled Current Age.  It assumes ATM that this field exists, but it could easily be extended to create the field if it does not.

Code: [Select]

<?php

// Script to find contacts with a birthday today and set a tag 'Birthdays Today'
// Also updates a variable 'Current Age' based on value of birth_date
// Call this at least nightly with cron so the tag & variable are always set correctly


if (module_exists('civicrm')) {
civicrm_initialize(TRUE);
// get month and day to search on
$timearray = getdate();
$BDday =  $timearray['mday'];
$BDmon =  $timearray['mon'];

// delete old tag and create a fresh copy
$params = array( 'name' => 'Birthdays Today', 'domain_id' => 1 );  
// TO DO: how do we really find the domain_id?
$oldtag = crm_get_tag($params);
if ( ! is_a( $oldtag, 'CRM_Core_Error' )) 
$out = crm_delete_tag(&$oldtag);
$tag = crm_create_tag($params);
if ( ! is_a( $tag, 'CRM_Core_Error' )) 
echo 'Tag created OK<br>';
else {
echo 'Cannot create tag! Giving up.<br>';
// print_r($tag);   // for debugging only
exit;
}

// get ids of contacts
$sql = "SELECT contact_id FROM civicrm_individual ".
"WHERE month(birth_date) = $BDmon and day(birth_date) = $BDday";
if (!$result = mysql_query( $sql )) {
echo 'No birthdays today<br>';
exit;
}

// for each id get the contact and tag it
$count = 0;
while ($item = mysql_fetch_assoc($result)) {
$contact = crm_get_contact($item);
if ( ! is_a( $contact, 'CRM_Core_Error' )) {
$entitytag = crm_create_entity_tag(&$tag, &$contact);
$count++;
} else {
echo 'Failed to tag contact id='.$params['contact_id'].'<br>';
}
}
echo "$count contacts tagged successfully<br>";

// get the custom field for Current Age
$params = array( 'label' => 'Current Age' ); 
$custom_field =& crm_get_custom_field( $params );
if ( is_a( $custom_field, 'CRM_Core_Error' )) {
echo 'Custom field error<br>';
// or create the field here & keep going ...
exit;
}

// select records with a birth_date 
$sql = "SELECT contact_id,TIMESTAMPDIFF(YEAR, birth_date, NOW() ) as age FROM civicrm_individual ".
"WHERE birth_date > '1900-01-01'";
if (!$result = mysql_query( $sql )) {
echo 'Error in age query<br>';
exit;
}

// loop through records and update the current age field
$count = 0;
while ($item = mysql_fetch_assoc($result)) {
$age = (int)$item['age'];
$cid = (int)$item['contact_id'];
$data = array ('value' => $age, 'contact_id' => $cid );  
$custom_value =& crm_create_custom_value( 'civicrm_contact', $cid, $custom_field, $data );
if (! is_a( $custom_value, 'CRM_Core_Error' )) {
$count++;
} else {
echo "Can't set custom_value<br>";
exit;
}
}
echo "Age updated for $count contacts<br>";

}

?>


[geilhufe]

Current age is a pretty well established field in most CRM systems. Used for birthdays and other searches. Since the code is there, maybe this would be a good added feature for CiviCRM 2.0.

Lobo, Dave?

[Donald Lobo]

There is an issue (resovled in 2.0) where we display the age in the view screen: http://issues.civicrm.org/jira/browse/CRM-2129

I suspect it makes more sense as a computed field from birth date rather than a static field. You can use mysql date functions in your query to get the age dynamically: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub

lobo

[twowheeler]

Yes that's true of course, although this approach is useful to us because 1) version 2.0 is not here yet and 2) my users were looking for a static field rather than a computed display item.  This way it is possible to quickly get a list of, say, all the contacts ages 16 to 18.  It is possible to work with the birth_date field directly to get this list, but more cumbersome.  They have to know how to figure out the birth date range that represents ages 16 to 18 and then how to make a search builder function to get that list.

I'm not suggesting it needs to be a permanent feature of civicrm.  But since it was useful to me I just thought I'd share it. 

[Piotr Szotkowski]

I’m pretty sure one should be easily able to create an SQL view with the age computed ‘on the fly’ by MySQL (based on the current day and birth date); this way one would end with the best of both worlds – a dynamic, self-updating age field seen from the outside of the database as a static column.