sync mailman with current members

[speleo]

Hi,

My org communicates between its members using a Mailman list. However synching between a membership listing in CiviCrm and mailman has been a manual pain. However mailman provides a tool to sync between a flat file of email addresses and its own internal database as described here http://www.tin.org/bin/man.cgi?section=8&topic=sync_members.

I’ve put together a simple script that will extract my current members from CiviCrm and output to a file which is then synced with mailman.

There are some limitations.
1.   You can’t maintain a list with both digest and non-digest option per member
2.   Bounce handling is a manual process
3.   You need to grant FILE rights to your user in mySQL

To use this script
1.   Create a directory to store the script such as /home/myusername/bin/
2.   Copy this script there calling it sync2mailman.php

Code: [Select]

<?php

// set database server access variables:
$host = "localhost";
$user = "your_db_username";
$pass = "passord";
$db = "civicrm_db_name"; 
$filename = "sync2mailman.txt";
$filedir = "/home/myusername/bin/";

// the Mailman list name is in the format list_domain
$mmlistname = "Test_mydomain.com"; 

// added the Mailman sync_members commands here
// see http://www.tin.org/bin/man.cgi?section=8&topic=sync_members for syntax
$mmcommands = "-n -w=no -g=no";

// *****************************************************
// no need to modify below these lines
// *****************************************************

// delete file if it exists as we can't output to an existing file
if(file_exists("$filedir$filename"))
{
if(unlink("$filedir$filename"))
{
echo "File was successfully deleted";
}
else
{
echo "File was not deleted.";
}
}
else
{
echo "File does not exist. No problem. Continuing.";
}


$link = mysql_connect($host, $user, $pass);
if (!$link) {
die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}

$query = "SELECT e.email 
FROM civicrm_contact AS c
LEFT JOIN civicrm_membership  AS mem  ON c.id=mem.contact_id 
LEFT JOIN civicrm_membership_type  AS mtype  ON mem.membership_type_id=mtype.id
LEFT JOIN civicrm_membership_status  AS mstatus  ON mem.status_id=mstatus.id
LEFT JOIN civicrm_email   AS e  ON c.id=e.contact_id AND e.is_primary = 1 

WHERE (mtype.id < 8 AND mstatus.id < 4 AND c.is_deceased =0)

ORDER BY e.email

INTO OUTFILE '$filedir$filename' ";

$result = mysql_query($query);

echo mysql_error();

// close connection
mysql_close($link); 

$output = `/usr/local/cpanel/3rdparty/mailman/bin/./sync_members $mmcommands -f $filename $mmlistname`;
echo "$output";

?>
3.   Make sure that your db_user has FILE rights. Unless you have this you will see and access denied error.
4.   Check the SQL statement to ensure it meets your setup. Especially look at mtype.id and mstatus.id values.
5.   Test the script to make sure it performs how you expect.
6.   Remove the –n $mmcommand to ensure that the changes are actually made
7.   Setup a cron. For example “php –f /home/mysuername/bin/sync2mailman.php

I'm sure that you can get all the db user info out of the civcrm config using some standard commands but I'd need to look into that. (Maybe someone else could step up to that!!??!).

For my enviroment with only a a few 100 users I don't think the bounce will be too hard to process manually but I suspect I'll need to write the select statement to filter out contact who don't want to be on the list....

Enjoy!

[Chris Burgess]

I suspect I'll need to write the select statement to filter out contact who don't want to be on the list....

This should do it:

Code: [Select]

WHERE is_opt_out = 0 AND do_not_email = 0


[barrylb]

Thanks very much to user speleo for his contribution and ideas. I have decided to implement a similar system but using the CiviCRM API instead of directly accessing MySQL.

I have multiple Mailman lists and my method is to sync with a CiviCRM Smart Group for each list. Underneath each smart group, I use a multi-select custom field, and other conditions, to choose which lists each person is subscribed to. Smart Groups can be quite easily changed and are quite flexible. I populate Mailman's real name field in addition to the email address.

I customised various Mailman pages and messages to point users to a custom page I wrote that allows people to unsubscribe or change their email address, instead of Mailman pages. My page works by having people type in their email address and they are sent a unique link to click to change their settings. The page updates their records via the CiviCRM API. I also disabled some of the Mailman email commands so people can't change their Mailman subscription via email commands.

I have the sync2mailman.php script run hourly. This is still in a testing phase at my site but it is looking good so far.

Code: [Select]

<?php
require_once '/path/to/civicrm.config.php';
require_once 'CRM/Core/Config.php';
require_once 'api/v2/Contact.php';

$config =& CRM_Core_Config::singleton();

global $tmpfilename;
global $mmparams;

$tmpfilename = '/home/myusername/sync2mailman.txt'; // Temporary file location

// Add the Mailman sync_members parameters here
// see http://www.tin.org/bin/man.cgi?section=8&topic=sync_members for syntax
$mmparams = "-n -w=no -g=no";

function SyncList($mmlistname, $GroupId) {
  global $tmpfilename;
  global $mmparams;

  // Open the temporary file
  $f = fopen($tmpfilename, 'w+');

  // Set search criteria
  $params['rowCount'] = 50000;
  $params['sort'] = 'sort_name ASC';
  $params['return.display_name'] = 1;
  $params['return.email'] = 1;
  $groups = array($GroupId => 1);
  $params['group'] = $groups;
  $result = civicrm_contact_search( $params );

  // Process the search results
  $nrows = 0;  
  foreach ($result as $id => $values) {
    if ($values['email'] != '') {
      $fix_displayname = str_replace('  ', ' ', $values['display_name']); // remove extra spaces in some names
      $line = trim($fix_displayname) . ' <' . trim($values['email']) . '>' . "\n";
      fwrite($f, $line);
      $nrows++;
    }
  }

  if ($nrows > 0) { // Zero rows is probably an error
    // Run the mailman sync_members command
    $command = "/usr/sbin/sync_members $mmparams -f $tmpfilename $mmlistname 2>&1";
    echo "Running $command\n";
    $output =  shell_exec($command);
    echo $output;
  }
  else
    echo "WARNING no rows... skipping sync command.\n";

  // Close the temporary file
  fclose($f);
}

SyncList('list_name','1'); // specify your mailing list name and CiviCRM group id here
// repeat for each list
?>


[speleo]

Barry,

That is much slicker than the cludge I threw together! Gives much greater control over handling multiple lists. Nice one!