CiviCRM Community Forums (archive)

I'm trying to do an ACL implementation similar to the NZ Green Party. But I'm getting a bit stuck at the best way to handle segmenting users into groups based on province/state. Somewhere on CiviCRM.org, I think in an architecture blog, Lobo indicates that it was quite efficient, and the example hook documentation appears similar to the NZ Green use case.

Looking at the example documentation for the hook http://wiki.civicrm.org/confluence/display/CRMUPCOMING/CiviCRM+hook+specification#CiviCRMhookspecification-hookcivicrmaclWhereClause, it seems that a custom field for province was created in the Region custom data group. Why was this not linked to the primary state/province field? Is it because of the inefficiency of using a JOIN to the primary Address record?

I'm trying to document how to do this at http://wiki.civicrm.org/confluence/display/CRMUPCOMING/Multi-level+Organization+ACL+Permissions and want to cover this very important use case properly.

TIA,
Joe

thanks, lobo

Thanks, lobo and Peter.

lobo, thanks for the suggestion on irc that I need to implement hook_civicrm_aclGroup as well as hook_civicrm_aclWhereClause. Unfortunately despite trying to get my head around the ACL system I'm still having trouble seeing how to implement this.

My issue is that I don't want to have to deal with groups for administrator/users and for contacts for each of 10 provinces, 308 federal ridings, and about 600 provincial ridings. As I understand it, the main advantages of this approach are to eliminate the burden of trying to manage so many groups in a UI designed for a small number, and also to avoid the scaling problems that result from having the system do all of the overhead of adding voters to groups. It doesn't seem like it will work to store 100k contact IDs in a smart group cache for a riding, or 9M for a province.

However, without putting something into the aclGroup hook, basic search with no parameters doesn't work properly. I'm stumped as to how to deal with this hook since it is not oriented towards SQL but rather towards Groups, which are something that doesn't seem appropriate. I've been imagining how one might implement a tokenized group, with the current user's contact.id as the token, and turn off the flag in the database for it to be considered a smart group based on a saved search which is dealt with via arrays of contact IDs that are cached. There seems to be a bit more stuff in the code than is exposed in the interface for groups and ACLs. I was thinking I might try something along the following lines: create a special group that requires a code hack to insert the contact_id of the current user inserted into the where clause in order to restrict the contacts returned appropriate.

The SQL for the query is as follows:
SELECT c.id, c.display_name, a.state_province_id, n.federal_riding_21, n.provincial_riding_154, `civicrm_value_permissions_9999`.* FROM `civicrm_contact` c
left join civicrm_address a on c.id=a.contact_id /* contains province info */
left join civicrm_value_name_1 n on c.id=n.entity_id, /* contains federal and provincial riding data for contacts */
civicrm_value_permissions_11 `civicrm_value_permissions_9999` /* contains the permission info for appropriate users, in multi-select fields */

where

15 /* this is the current user's contact_id, and needs to be replaced each time the group is loaded. */

=`civicrm_value_permissions_9999`.entity_id AND ( ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.provincial_section_admin_158), IF(NOT ISNULL(a.state_province_id), instr(`civicrm_value_permissions_9999`.provincial_section_admin_158,a.state_province_id)>1, FALSE), FALSE) ) OR ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.federal_riding_admin_156),
IF(NOT ISNULL(n.federal_riding_21), instr(`civicrm_value_permissions_9999`.federal_riding_admin_156,n.federal_riding_21)>1, FALSE), FALSE) ) OR ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.provincial_riding_admin_157), IF(NOT ISNULL(n.provincial_riding_154), instr(`civicrm_value_permissions_9999`.provincial_riding_admin_157,n.provincial_riding_154)>1, FALSE), FALSE) ) );

Doing some manual work pulling this apart into the pieces for civicrm_group, I get (sorry for the serialized data - I hope it makes sense):

"id","name","title","description","source","saved_search_id","is_active","visibility","where_clause","select_tables","where_tables","group_type","cache_date","parents","children","is_hidden"
"25","Permissions","Permissions",NULL,NULL,NULL,"1","User and User Admin Only","=`civicrm_value_permissions_9999`.entity_id AND ( ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.provincial_section_admin_158), IF(NOT ISNULL(a.state_province_id), instr(`civicrm_value_permissions_9999`.provincial_section_admin_158,a.state_province_id)>1, FALSE), FALSE) ) OR ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.federal_riding_admin_156),
IF(NOT ISNULL(n.federal_riding_21), instr(`civicrm_value_permissions_9999`.federal_riding_admin_156,n.federal_riding_21)>1, FALSE), FALSE) ) OR ( IF(NOT ISNULL(`civicrm_value_permissions_9999`.provincial_riding_admin_157), IF(NOT ISNULL(n.provincial_riding_154), instr(`civicrm_value_permissions_9999`.provincial_riding_admin_157,n.provincial_riding_154)>1, FALSE), FALSE) )
)","a:11:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:22:\"civicrm_state_province\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:23:\"civicrm_value_name_9999\";s:110:\" left join civicrm_value_name_1 `civicrm_value_name_9999` on contact_a.id=`civicrm_value_name_9999`.entity_id \";s:28:\"civicrm_value_permissions_11\":i:1;s:6:\"gender\";i:1;}","a:4:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:25:\"`civicrm_value_name_9999`\";s:110:\" left join civicrm_value_name_1 `civicrm_value_name_9999` on contact_a.id=`civicrm_value_name_9999`.entity_id \";
s:28:\"civicrm_value_permissions_11\":i:1;}",NULL,NULL,NULL,NULL,"0"

Anyway, I'd appreciate some feedback on how to implement this since I'm afraid I may be barking up the wrong tree. I also see that the folks from US PIRG are examining other approaches; do you have a sense of whether their primary use cases are the similar enough to those involved in voter tracking / GOTV?

ACL code to restrict which contacts you can view (based on custom data, but easy to do if you used core location data for same purpose).

/**
 * Implement CiviCRM's hook_civicrm_aclWhereClause
 *
 * restrict users to contacts they are granted access to via "Regional Access" custom data tab
 *
 * restrict users from viewing contacts flagged with the "VIP" flag (under special data)
 *
 * only users with permission to skip ACL checks entirely will have access to all regions and to VIP contacts
 */
function mymodule_civicrm_aclWhereClause( $type, &$tables, &$whereTables, &$contactID, &$where ) {
  if ( ! $contactID ) {
      return;
  }

  $permissionTable = 'civicrm_value_1_regional_access_17';
  $regionTable     = 'custom_value_1_Geography';
  $perms = array(
    array(
      'perm_field'   => 'electorates', // viewing user may access these electorates
      'region_field' => 'Electorate',   // target contact is in this electorate
      'type'         => 'String',
    ),
    array(
      'perm_field'   => 'provinces', // viewing user can access these provinces
      'region_field' => 'Province', // target contact is in this province
      'type'         => 'String',
    ),
    array(
      'perm_field'   => 'branches', // viewing user may access these branches
      'region_field' => 'Branch', // target contact is in this branch
      'type'         => 'String',
    ),
  ) ;
  
  // get all the values from the permission table for this contact
  foreach( $perms as $p ) {
    $keys[] = $p['perm_field'] ;
  }
  $keys = implode( ', ', $keys );
  $sql = "
SELECT $keys
FROM   {$permissionTable}
WHERE  entity_id = $contactID
";
  $dao = CRM_Core_DAO::executeQuery( $sql, CRM_Core_DAO::$_nullArray );
  if ( ! $dao->fetch( ) ) {
    return;
  }

  $tables[$regionTable] = $whereTables[$regionTable] =
      "LEFT JOIN {$regionTable} regionTable ON contact_a.id = regionTable.entity_id" ;

  $clauses = array( );
  foreach( $perms as $perm ) {
    if ( !empty( $dao->$perm['perm_field'] ) ) {
      if ( strpos( CRM_Core_DAO::VALUE_SEPARATOR, $dao->$perm['perm_field'] ) !== false ) {
        $value = substr( $dao->$perm['perm_field'], 1, -1 );
        $values = explode( CRM_Core_DAO::VALUE_SEPARATOR, $value );
        foreach ( $values as $v ) {
          $clauses[] = "regionTable.{$region_field} = $v";
        }
      } else {
        if ( $perm['type'] == 'String' ) {
          $clauses[] = "CONCAT('%".
            CRM_Core_DAO::VALUE_SEPARATOR.
            "',regionTable.".$perm['region_field'].
            ",'".
            CRM_Core_DAO::VALUE_SEPARATOR.
            "%') LIKE '%".
            "{$dao->$perm['perm_field']}".
            "%'";
        } else {
          $clauses[] = "regionTable.{$perm['region_field']} = {$dao->$perm['perm_field']}";
        }
      }
    }
  }

  if ( ! empty( $clauses ) ) {
    if ( strlen(trim($where)) != 0 ) {
      $where .= ' AND ' ;
    }
    $where .= '(' . implode( ' OR ', $clauses ) . ')' ;
  }

  // add VIP restriction - this prevents most CiviCRM users accessing contact details
  // for certain flagged contacts
  $tables[$specialTable] = $whereTables[$specialTable] =
    "LEFT JOIN custom_value_1_Special_Data  specialTable " .
      "ON contact_a.id = specialTable.entity_id" ;
  $where .= ' AND ( specialTable.vip_status != 1 OR specialTable.vip_status IS NULL )' ;

}

Tab restriction code (prevents contacts from accessing tab where regional permissions are specified):

function mymodule_civicrm_tabs( &$tabs, $contactID ) {
  $restricted = array(
    // Drupal permission which is required for these tabs
    'headoffice view' => array(
      // and the tabs which are granted with this perm
      'custom_17', // regional access
      'custom_8',  // special data
    ),
  ) ;
  foreach( $restricted as $perm => $tabs_restricted ) {
    if ( !user_access($perm) ) {
      foreach( $tabs as $id => $tab ) {
        if ( in_array($tab['id'], $tabs_restricted) ) {
          unset($tabs[$id]) ;
        }
      }
    }
  }
}