Wednesday, October 17, 2012

Time only goes forward, or does it? A time turner for civimail bad links!

Recently a client sent out an email with a bad link in it.

Normally, I'd sympathise with them but tell them that the horse has bolted and there's no point in closing the barn doors (because I like to use old fasioned expressions).  (And yes - if the link was on my server, I could give them an apache redirect.)

But, if the message went out via civimail and you're tracking click-throughs of links (actually this trick also works with simplenews if you're using simplenews_stats), then there's still hope.

This is because the actual link embedded in the mail isn't the final destination, but a civicrm url with a magic trackable url id, which then redirects to the final url, which is stored in a civicrm table. The rest you can probably do as a simple homework exercise, but here's the details since I've got more room for this post.

Start with loading up a mysql client and use your civicrm database, then find out the right id using this:

select * from civicrm_mailing_trackable_url order by mailing_id desc limit 30;

The first thing you'll want to do is fix that with something like

update civicrm_mailing_trackable_url set url = 'your-corrected-url' where id = the-id-of-the-bad-url;

If you caught it early enough, then you're done, but you might want to try this query as well:

select distinct contact_id from civicrm_mailing_event_trackable_url_open o inner join civicrm_mailing_event_queue q on o.event_queue_id = where trackable_url_id = the-id-of-the-bad-url;

Which will give you a list of the contact ids of people who have already clicked through your bad url - in which case you can resend them the corrected version and some humble pie.

Friday, October 12, 2012

CiviCRM user and administrator training in Toronto, November 14-15 2012

A CiviCRM user and administration training in Toronto has just been announced for Nov 14-15, here:

If, like most people including myself, you wish you understood a bit more about this multi-headed beast called CiviCRM, then this should be a great opportunity.

If you've grown a little complacent with your CiviCRM install, this is a great place to learn about all the new things you can do with it, and hear what others like you are already doing. Non-profit types already know that community is an essential part of success and sustainability, and events like this put that into practice within the CiviCRM ecosystem.

In addition, there's a developer training and a 'code sprint', for the weeks following.

And it reminds me that it's past time to publish a new civicrm integration module I wrote this year.

Thanks to Joe Murray and Louis-Charles Lavallee for getting this going!

Thursday, October 04, 2012

Drupal Commerce IATS

As faithful readers of this blog will know, I'm a fan of IATS as a payment processor solution for non-profits. Many years ago I wrote a CiviCRM payment plugin that is still in wide use by most of my clients and many others as well.

Although my Drupal module contributions have been minimal over the recent past, I'm happy to announce a new one to integrate IATS payments into the new Drupal Commerce module/distro/framework/thingy.


Many thanks to Karin Gerritson, my colleague at Blackfly Solutions, and her project to develop a new site for the school she sends here kids to, here:

What this means for the rest of the world: non-profits have a nice solution to get e-commerce going on Drupal 7 with a minimum of cost and hassle.

Drupal 7 migration: a script for image + image_attach

Inspired by this post:

I thought I'd dig out and contribute a script I wrote for a similar issue I had with a number of old sites.

Specifically - in the old days before imagecache, one way to safely enable your site editors to add pictures to posts was using a combination of the image module with the image attach module. Functionally, most of the sites I've seen it on were doing the same thing as the imagecache, only not as well, and now that D7 is mainstream I can't prolong keeping it going any longer.

The main difference with Laura's post above is trying to deal with the image attachment bit - i.e. getting the images into the fields of the posts to which the original image nodes where being 'attached'.

The other key difference - this script was written for D6, so do this before you do your D7 upgrade. It's making use of the D6 table structure, it's not written using high-level api's! D7's table structure is much more complicated, so trying to do this after the D7 upgrade would be more difficult I suspect.

Also: doing this only with a script would be too general a tool I think. In any case, I simplified the problem by manually creating the new image fields and then hard coding those field names into my script.

Conclusion: feel free to recycle my script below, but make sure you

1. create the new fields first, and then modify my "$convert" array below.
2. try it on a dev copy first. Also, you'll see some commented out code that I uncomment on the first run as a way of checking out what it will do.

Notes: because an image node image can be attached to multiple nodes, i'm copying the original image files, not reusing them in their same location. So when you're done, you can remove the files/image directory where the image module put it's stuff.

// code to convert image module use to imagecache

$convert = array(
  'newsletter_article' => 'field_newsletter_image',
  'page' => 'field_page_image',
  'press' => 'field_press_image',

// you can run this code first to see what sizes were being attached to which node types
/* foreach(node_get_types() as $type => $info) {
  $image_attach = variable_get("image_attach_$type",'');
  if ($image_attach) {
    echo "\n $type";   
    echo db_result(db_query('DESC content_type_'.$type));
} */
$sizes = variable_get('image_sizes','');
// print_r($sizes); die();

foreach($convert as $type => $image_field) {
  //echo $image_field;
  $iff = $image_field.'_fid';
  $config = db_fetch_object(db_query("SELECT * FROM {content_node_field_instance} WHERE field_name = '%s'",$image_field));
  $settings = unserialize($config->widget_settings);
  $dir = file_create_path($settings['file_path']);
  // print_r($config);
  // print_r($settings);
  $status = file_check_directory($dir,(FILE_CREATE_DIRECTORY | FILE_MODIFY_PERMISSIONS));
  if (!$status) {
    die("Unable to create directory!!!");
    // TODO: for rfc, i don't have directory create permission, need to run initialization as nobody
  // for each image, move the _original one to the new directory, 
  // remove the other ones, and update the db
  $result = db_query("SELECT n.vid,ia.*,i.fid FROM {node} n INNER JOIN {image_attach} ia ON n.nid = ia.nid INNER JOIN {image} i on ia.iid = i.nid AND i.image_size = '_original' WH
ERE n.type = '%s'",$type);
  while ($ia = db_fetch_object($result)) {
    $file = db_fetch_object(db_query("SELECT * FROM files where fid = %d",$ia->fid));
    $name = basename($filepath = $file->filepath);
    // print_r($ia);
    // die("$old_path -> $new_path");
    // get the current fid!
    $good = file_copy($filepath,$settings['file_path'],FILE_EXISTS_REPLACE);
    if (!$good) {
      print_r($file); print_r($ia); die($filepath);
    else {
      db_query("INSERT INTO {files} (uid,filename,filepath,filemime,filesize,status,timestamp) VALUES (%d,'%s','%s','%s',%d,%d,%d)",$file->uid,$name,$filepath,$file->filemime,$fil
      $fid = db_last_insert_id('files','fid');
      if (!db_result(db_query("SELECT vid FROM {content_type_$type} WHERE vid = %d",$ia->vid))) {
        db_query("INSERT INTO {content_type_$type} (nid,vid,$iff) VALUES (%d,%d,%d)",$ia->vid, $ia->nid, $fid);
      else {
        db_query("UPDATE {content_type_$type} SET $iff = %d WHERE vid = %d",$fid, $ia->vid);
    db_query('DELETE FROM {image_attach} WHERE nid = %d AND iid = %d',$ia->nid,$ia->iid);
    // print_r($ia); die("$old_path $new_path");
  // echo $status ? ': good' : ': bad';
// print_r(array_keys($types));