Author Topic: Announcement: New version of "region lookup" extension (and CSV import question) (Read 3042 times)

mathieu · June 19, 2013, 08:12:39 am

I started working on "porting" to a standard CiviCRM extension the "civicrm region lookup" module that was previously specific to Drupal.

* new: https://github.com/mlutfy/ca.bidon.regionlookup
* old: https://github.com/mlutfy/CiviCRM-RegionLookup

The extension provides an easy way to "lookup" a value for a given key. The typical use-case is when the user enters a postcode, an ajax request is sent to the server to determine what is the district, state, or some custom value for that postcode (ex: closest hospital or related clinic, as long as there is a mapping for postcode => clinic).

It currently supports most of what the old module did, and is way simpler to use, since it doesn't depend on "services" anymore for the ajax part.

Question: the old module integrated with the Feeds drupal module to allow the user to upload a CSV file with the value mappings (postcode to value). Any recommendations on how to do that in CiviCRM? Worst case, I'll just create an administrative form that handles the upload of a CSV file is a specific format.

The new extension is still work in progress, but is usable in production (you can upload the CSV file through phpmyadmin). Feedback welcomed

PS: thanks to Lola (freeform) and Nicolas Ganivet (cividesk) for their work on the "report error" extension during the last SF code sprint (https://github.com/mlutfy/ca.bidon.reporterror), I could re-use a lot of code to quickly figure out how to handle settings and admin config forms.

SarahG (FountainTribe) · June 19, 2013, 08:37:42 pm

Mathieu - Thanks for sharing this extension. I wrote a small batch PHP script designed to be used with your extension. What it does is fill in the field "civicrm_address.county_id" for existing addresses. In my environment, I added it to a CRON job to run automatically. Perhaps you could package this with your extension.

The source for the batch script is below:

Code: [Select]

global $civicrm_root;
	
	if ( file_exists( '../../administrator/components/com_civicrm/' ) ) {
		// This is a Joomla site. 
    		$civicrm_root = '../../administrator/components/com_civicrm/civicrm/';
	} else {
		// This is a Drupal site.
    		$civicrm_root = '../../sites/all/modules/civicrm/';
	}



       $tmp_config = $civicrm_root.'civicrm.config.php';
        require_once $tmp_config;

    require_once 'CRM/Core/Config.php';
    $config =& CRM_Core_Config::singleton( );
	

    require_once 'CRM/Core/Error.php';
    
      $rec_count = 0; 

	$sql = "SELECT  a.id as address_id  ,  c.id as new_county_id ,
		a.county_id as old_county_id,
		 civicrm_contact.display_name, a.contact_id , 
		a.postal_code, r.postcode, r.county, 
		  s.name as state_name, 
		  civicrm_country.iso_code
		FROM civicrm_address a
		LEFT JOIN civicrm_contact ON civicrm_contact.id = a.contact_id 
		JOIN  civicrm_state_province s  ON a.state_province_id = s.id
		LEFT JOIN civicrm_country ON s.country_id = civicrm_country.id
		LEFT JOIN civicrm_regionlookup r ON a.postal_code = r.postcode AND r.country = civicrm_country.iso_code
		LEFT JOIN civicrm_county c ON lower(c.name) = lower(r.county) AND c.state_province_id = a.state_province_id 
		WHERE 
		( a.county_id is null OR a.county_id <> c.id)
		AND a.postal_code is not null 
		AND a.state_province_id is not null
		AND a.contact_id is not null 
		AND c.id is not null
		group by a.id"; 
		
	
	$dao =& CRM_Core_DAO::executeQuery( $sql,   CRM_Core_DAO::$_nullArray ) ;
		
	while($dao->fetch()){	
		$address_id = $dao->address_id ; 
		$county_id = $dao->new_county_id; 
	
		$update = "UPDATE civicrm_address set county_id = $county_id WHERE id = ".$address_id ;
		
		
		$dao_update = & CRM_Core_DAO::executeQuery( $update,   CRM_Core_DAO::$_nullArray ) ;
		$dao_update->free();
		 $rec_count +=1; 
	
	}
	
	$dao->free(); 
	
	print "<h2>All done. Number of addresses updated: ".$rec_count."</h2>" ;

Thanks,
Sarah Gladstone
pogstone.com

mathieu · June 20, 2013, 06:05:54 am

Thanks for the feedback. I also use similar crons on some installs, but I wasn't sure how to generalise it so that it can be used in various use cases (I mostly use the module to fill in a custom field).

I'm looking forward to see how people use the module, and we can probably support a few of the most common use-cases (such as counties), and have a standard civicrm "job" that could be extended.

joemcl · December 06, 2013, 06:29:55 am

Installed the extension last night, crashing and getting this message, disable not working. Any help really appreciated!

Fatal error: Call to undefined method CRM_Core_Resources::addSetting() in /var/www/citizenactionny/my.citizenactionny.org/sites/default/civicrm_extensions/ca.bidon.regionlookup/regionlookup.php on line 127

mathieu · December 06, 2013, 07:23:23 am

Hi,

Thanks for the report. Your profile mentions CiviCRM version 4.2.8, is that the case?

Mathieu

joemcl · December 06, 2013, 09:46:16 am

Actually on 4.2.10 now. Can not disable it to uninstall. Same error message when click on disable. It's crashing my whole installation. Help please! Thanks.

joemcl · December 06, 2013, 10:05:02 am

The hosted server with Fuzion is on nginx so the below note from your GitHub might be the issue, but hard for me to tell from the error message.

Using nginx/1.4.4, Database client version: libmysql - mysqlnd 5.0.8-dev - 20102224 - $Id: 731e5b87ba42146a687c29995d2dfd8b4e40b325 $

"Warning for nginx =================

Under the nginx web server, you may need to add a configuration to
allow .json requests to pass. Otherwise the .json requests will return
a 404 error code (c.f. your Firebug network console).

I have limited experience with nginx, so if you have found the correct
config fix for this, please let me know so we can document it."

mathieu · December 06, 2013, 10:05:42 am

Hi Joe,

Sorry for the inconveniences that this is causing. Seems like CiviCRM 4.2 doesn't have the 'addSetting' function that is required for the extension to work well.

There may be an easier way to disable the extension, but one way to do it, would be to:

* edit the file regionlookup.php
* find the function "regionlookup_civicrm_buildForm"
* add a 'return;' statement anywhere in there.

For example:

Code: [Select]

/**
 * Implements hook_civicrm_buildForm().
 *
 * Injects the settings to know on which field to trigger the lookup.
 * Assumes that this module runs only on proper forms, not pages.
 */
function regionlookup_civicrm_buildForm($formName, &$form) {
    return;
[...]
}

I'm back at my computer if you want to try calling again.

mathieu · December 06, 2013, 10:30:28 am

We resolved this by phone, by removing the MySQL database table record for the extension in the "civicrm_extension" table using phpmyadmin (accessing the files directly was not possible).

I modified the extension code to remove compatibility with 4.2.

joemcl · December 06, 2013, 10:34:59 am

mathieu, thanks so so much for your help in getting it disabled. I really look forward to reinstalling it and using it when we move to 4.3+

CiviCRM Community Forums (archive)

News:

Author Topic: Announcement: New version of "region lookup" extension (and CSV import question) (Read 3042 times)

mathieu

Announcement: New version of "region lookup" extension (and CSV import question)

SarahG (FountainTribe)

Re: Announcement: New version of "region lookup" extension (and CSV import question)

mathieu

Re: Announcement: New version of "region lookup" extension (and CSV import question)

joemcl

Re: Announcement: New version of "region lookup" extension (and CSV import question)

mathieu

Re: Announcement: New version of "region lookup" extension (and CSV import question)

joemcl

Re: Announcement: New version of "region lookup" extension (and CSV import question)

joemcl

Re: Announcement: New version of "region lookup" extension (and CSV import question)

mathieu

Re: Announcement: New version of "region lookup" extension (and CSV import question)

mathieu

Re: Announcement: New version of "region lookup" extension (and CSV import question)

joemcl

Re: Announcement: New version of "region lookup" extension (and CSV import question)