In previous post you learnt how to create REST API to fetch data from SugarCRM application, in this tutorial you will learn to migrate data from Salesforce to SugarCRM application. Whenever you migrate data from Salesforce to an existing SugarCRM application, it would be good to write two entry points: First create a table for each Salesforce spreadsheet and dump the data in the table. That way you could access it much more easily and clean/translate what needed translating before import: It read the directory with the Salesforce csv files and for each file create a table with name Once you get satisfied that you had your For each module create a map of Salesforce to sugar fields, just an array. Then create a generic Try to keep the For each record of the sfrc table the function would process each field of that module, mapping the Salesforce fields to the sugar fields, translating the sf_ fields like dropdown values via array maps, create relationships where needed (by building arrays that were processed after the bean was saved and available), create additional beans like email addresses as needed etc (the order in which the tables were processed was chosen explicitly Accounts then Contacts, for example, because you need an Account to relate the contact to). Each time a record is entered it will be logged in a special As Sugar is an existing implementation also include duplicate checks for Accounts, Contacts, Leads and recorded the duplicate in the The sfrc_ tables will help you in case someone feels that something got lost in the process. Doing it this way will also allow you to create new SugarIDs for every record thus keeping things a little cleaner. The two functions shared above are instrumental in getting things to work smoothly without hardcoding relationships.How to parse Salesforce tables and put data in SugarCRM?
Last modified
sfrc_TheNameOfTheFileWithoutExtension and name the fields as in the first line of the file (the headers aka the Salesforce fields) then proceed to do inserts for each row of the csv.sfrc_SFTableName tables all set up then write an import script.
The LHS of the map is the salesforce field, the RHS is coded:
if it started with sf_ then it needed some kind of translation, if it started with new_ then it needed creating anew, everything else was an exact map (i.e. put the SF value in that Sugar field name as is).import_data function with parameters the Salesforce table, the map, and the corresponding sugar Class. e.g:import_data('sfrc_Account', $account_map, 'Account');import_data as generic as possible so you can add calls for different modules with little more than processing special cases for special fields.sfrc_reference table you created which has the salesforceID, the sugarID, the salesforceModule and the SugarModule. This will allow you to cross reference Salesforce and Sugar data and make sure that the data is coming across appropriately.sfrc_reference table so the sfrc record that already existed in Sugar is associated with an existing Sugar record and the duplicate flag in the reference table is set to 1. We could then check duplicates to make sure they are indeed that. Any further information related to that duplicate record is associated with the existing record instead of creating a new one.
<?php
// Find the Name of the Relationship between two Modules
function getRelationshipByModules ($m1, $m2)
{
global $db,$dictionary,$beanList;
$rel = new Relationship;
if($rel_info = $rel->retrieve_by_sides($m1, $m2, $db)){
$bean = BeanFactory::getBean($m1);
$rel_name = $rel_info['relationship_name'];
foreach($bean->field_defs as $field=>$def){
if(isset($def['relationship']) && $def['relationship']==$rel_name) {
return(array($def['name'], $m1));
}
}
} elseif($rel_info = $rel->retrieve_by_sides($m2, $m1, $db)){
$bean = BeanFactory::getBean($m2);
$rel_name = $rel_info['relationship_name'];
foreach($bean->field_defs as $field=>$def){
if(isset($def['relationship']) && $def['relationship']==$rel_name) {
return(array($def['name'], $m2));
}
}
}
return(FALSE);
}
// Cool function to relate beans together automatically.
function link_record_beans($bean1, $bean2){
$m1 = $bean1->module_dir;
$m2 = $bean2->module_dir;
$rel=getRelationshipByModules($m1, $m2); //see link to previous post for this function
if($rel !== FALSE){
if($rel[1] == $m1){
$class = $bean1->object_name;
$id = $bean1->id;
$rel_id = $bean2->id;
}elseif($rel[1] == $m2){
$class = $bean2->object_name;
$id = $bean2->id;
$rel_id = $bean1->id;
}
$lhs = new $class;
$lhs->retrieve($id);
$lhs->load_relationship($rel[0]);
$lhs->$rel[0]->add($rel_id);
$lhs->save();
}
}
Creating tables from Salesforce csv dumps:
<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 'On');
function escape_quotes($n)
{
$tmp1 = $n;
$tmp2 = preg_replace('/\\\/', '\\\\\\', $tmp1);
$tmp = preg_replace("/'/", "\'", $tmp2);
return ($tmp);
}
$directory = '<pathToSalesforceCVSFiles>/SFRecords/';
global $db;
if (!is_dir($directory)) {
exit('Invalid diretory path');
}
$files = array();
foreach (scandir($directory) as $file) {
echo ("processing {$file}<p>");
//Get file and create table based on first row column names
if ('.' === $file)
continue;
if ('..' === $file)
continue;
if ('..' === 'Attachments')
continue;
$create_fields = '';
$table = 'sfrc_' . str_replace('.csv', '', $file);
if (($handle = fopen("{$directory}{$file}", "r")) !== FALSE) {
echo ("opening {$file}<p>");
$records = array();
while (($data = fgetcsv($handle, "", ",")) !== FALSE) {
$records[] = $data;
}
$fields = array();
$fields = array_shift($records);
$fields_string = implode(", ", $fields);
foreach ($fields as $field) {
$field = trim(str_replace('"', '', $field));
if ($field == 'Id')
$create_fields .= " {$field} varchar(255) NOT NULL UNIQUE,";
elseif ($field == 'Description')
$create_fields .= " {$field} text,";
else
$create_fields .= " {$field} varchar(255),";
}
$create_fields = rtrim($create_fields, ',');
$create = " CREATE TABLE IF NOT EXISTS {$table} ({$create_fields}); ";
echo ($create . "<br>");
$res = $db->query($create);
if (!isset($res) || $res != 1)
echo ("ERROR CREATING TABLE $table <p>");
else
echo ($table . " created <p>");
//Load Data
$i = 1;
foreach ($records as $record) {
$values = "'" . implode("', '", array_map('escape_quotes', $record)) . "'";
$insert = "INSERT into {$table}({$fields_string}) VALUES ({$values})";
$result = $db->query($insert);
if (!isset($result) || $result != 1) {
echo ("Row $i, PROBLEM WITH<br>$insert<p>");
echo ("<PRE>");
var_dump($record);
echo ("</PRE>");
}
echo ("{$i} RESULT:" . $result . "<p>");
$i++;
}
fclose($handle);
}
}
echo ("Done <P>");
?>
Parsing Salesforce tables and loading data into SugarCRM:
<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 'On');
echo('<pre>');
include_once('custom/entry_points/getRelationshipByModules.php');
global $db, $current_user;
$log = '';
function checkForDuplicates($dbean){
global $db;
$now = gmdate("Y-m-d H:i:s");
$logfile = "logs/sf_import_log.log";
$dup_found = false;
$dup_id = '';
$dmodule_class = $dbean->object_name;
//see module/moduleFormBase.php for a starting point
if($dmodule_class == 'Account'){
$check = //your select statement to check for the duplicates for each class of items
}elseif($dmodule_class == 'Contact'){
$check = //your select statement for
}else{
return array(false, '');
}
if(isset($check)){
$result=$db->query($check);
$sfrc_id = isset($dbean->pg_salesforce_id_c)?$dbean->pg_salesforce_id_c:" see {$dmodule_class}";
while($row=$db->fetchByAssoc($result)){
$log = "{$now}:: Potential Dup {$dmodule_class}:</b> name = {$row['name']}; sugar_id = {$row['id']}; salesforce_id = {$sfrc_id} \n";
file_put_contents($logfile, $log, FILE_APPEND);
$dup_found = true;
$dup_id = $row['id'];
}
}
echo($dup_found);
echo"<p>";
return array($dup_found, $dup_id);
}
function get_parent($sf_id){
global $db;
$lookup="SELECT sugar_id, sugar_module FROM sfrc_reference where sf_id = '{$sf_id}'";
$result=$db->query($lookup);
$row=$db->fetchByAssoc($result);
if(empty($row)) return(false);
//check that it really exists
$mod = $row['sugar_module'];
$id = $row['sugar_id'];
if(empty($id) || empty($mod)) return(false);
$pbean = new $mod;
$pbean->retrieve($id);
if(empty($pbean)) return(false);
return array($mod, $id);
}
// get the sugarcrm bean or id based on SF id
function get_sugar_bean($sf_id, $class, $return_id=false){
global $db;
$lookup="SELECT sugar_id FROM sfrc_reference where sf_id = '{$sf_id}' and sugar_module = '{$class}'";
$result=$db->query($lookup);
$row=$db->fetchByAssoc($result);
if($return_id) return($row['sugar_id']);
$sbean = new $class();
$sbean->retrieve($row['sugar_id']);
if(empty($sbean)) return(false);
return($sbean);
}
// orig created as salesforce
function get_sugar_user_id($sf_user_id){
global $db, $current_user;
//get default user
$default_user = new User();
$default_user->retrieve_by_string_fields(array('user_name'=>'amberleigh'));
$default_user_id = $default_user->id;
$find_user = "SELECT UserName FROM sfrc_User WHERE Id = '{$sf_user_id}'";
$sf_find = $db->query($find_user);
//if there is no such user in Salesforce return the default user
if($sf_find === false) return($default_user_id);
$u = $db->fetchByAssoc($sf_find);
list($username,$domain) = explode('@',$u['UserName'] . "@");
if($username == 's.wolfram') $username = 'sw';
$find_sugar_user = " SELECT id FROM users WHERE user_name = '{$username}'";
$sugar_find = $db->query($find_sugar_user);
//if there is no such user in Sugar return the default user
if($sugar_find === false) return($default_user_id);
$su = $db->fetchByAssoc($sugar_find);
$sugar_user_id = isset($su['id'])?$su['id']:$default_user_id;
return($sugar_user_id);
}
function link_record_beans($bean1, $bean2){
if(empty($bean1) || empty($bean2)) return(false);
$m1 = $bean1->module_dir;
$m2 = $bean2->module_dir;
$rel=getRelationshipByModules($m1, $m2);
if($rel !== FALSE){
if($rel[1] == $m1){
$class = $bean1->object_name;
$id = $bean1->id;
$rel_id = $bean2->id;
}elseif($rel[1] == $m2){
$class = $bean2->object_name;
$id = $bean2->id;
$rel_id = $bean1->id;
}
$lhs = new $class;
$lhs->retrieve($id);
$lhs->load_relationship($rel[0]);
$lhs->$rel[0]->add($rel_id);
$lhs->update_date_modified = false;
$lhs->update_modified_by = false;
$lhs->set_created_by = false;
$lhs->update_date_entered = false;
$lhs->save();
//setTeamsAndBusinessRegion($class, $lhs->id);
}
return;
}
// IMPORT function
function import_data($sfrc_table, $field_map, $module_class){
global $db, $current_user,$beanList;
//where the actual attchments are
$sf_attach_directory = '/var/www/html/SFRecords/Attachments/';
//set teams: Contacts, Accounts, Addresses should be Global, PG
//Opportunities, Leads, Tasks should be PG only.
$now = gmdate("Y-m-d H:i:s");
//userID fields need loopu in the Users table in sugarcrm
$userID_fields = array('OwnerId','CreatedById' ,'LastModifiedById');
//link_fields will require looking up the corresponding sugarid of that parent to create the link in Sugar
$link_fields = array(
'AccountId'
,'ContactId'
,'ConvertedAccountId'
,'ConvertedContactId'
,'ConvertedOpportunityId'
//,'ReportsToId'
);
//dropdown maps needed to translate values from salesforce to sugar
//map Lead Status
$map_lead_status= array(
'Open'=> 'New',
'Qualified' => 'Assigned',
//etc....
);
//loop through records, for each build and save a bean.
$sfrc_query = "SELECT * from {$sfrc_table} ";
$sfrc_result=$db->query($sfrc_query);
while($item=$db->fetchByAssoc($sfrc_result)){
//reset all temporary values
$link_records = array();
$opp_stage = '';
$concat_prods_of_int = array();
$sugar_id = '';
$is_duplicate = '0';
unset($sugar_bean);
unset($sea);
unset($bean_to_load);
unset($dup_bean);
//
$bean_to_load = new $module_class();
//if it's an Attachment make sure the file exists else skip this record
if($module_class == 'pgdoc_Partnership_Documents' && !file_exists($sf_attach_directory.$item['Id'])) continue;
//loop through fields
foreach($item as $sf_field => $field_value){
$field_value = trim($field_value);
//populate the values in the map that are not null
//don't consider the default values Salesforce throws in for blanks '000000000000000AAA'
if(array_key_exists($sf_field, $field_map) && $field_value != '000000000000000AAA'){
//deal with all the special sf_ fields that need translations etc.
if($sf_field == 'Id'){
$salesforce_id = $field_value;
}elseif($sf_field == 'WhatId' && $field_value != ''){
$p = false;
$p = get_parent($field_value);
if(!empty($p))list($parent_type, $parent_id) = $p;
if (isset($parent_type) && isset($parent_id) && !empty($parent_type) && !empty($parent_id)){
$bean_to_load->parent_type = array_search($parent_type, $beanList);
$bean_to_load->parent_id = $parent_id;
}
}elseif($sf_field == 'WhoId' && $field_value != ''){
$p = false;
$p = get_parent($field_value);
if(!empty($p))list($parent_type, $parent_id) = $p;
if(isset($parent_type) && isset($parent_id) && $parent_type == 'Contact' && !empty($parent_id)){
if($module_class == 'Task'){
$bean_to_load->contact_id = $parent_id;
}elseif($module_class == 'Meeting' || $module_class == 'pgdoc_Partnership_Documents'){
$sb = false;
//get the sugar bean to link the records
$sb = get_sugar_bean($parent_id, $module_class);
if(!empty($sb)) $link_records[] = $sb;
}
}elseif(isset($parent_type) && isset($parent_id) && !empty($parent_type) && !empty($parent_id)){
$bean_to_load->parent_type = array_search($parent_type, $beanList);
$bean_to_load->parent_id = $parent_id;
}
}elseif($field_map[$sf_field] == 'sf_created_by' && $field_value != ''){
$created_by = get_sugar_user_id($field_value);
$bean_to_load->created_by = $created_by;
}elseif($field_map[$sf_field] == 'sf_modified_user_id' && $field_value != ''){
$modified_by = get_sugar_user_id($field_value);
$bean_to_load->modified_user_id = $modified_by;
}elseif($field_map[$sf_field] == 'sf_owner_user_id' && $field_value != ''){
$assigned_user = get_sugar_user_id($field_value);
$bean_to_load->assigned_user_id = $assigned_user;
}elseif($field_map[$sf_field] == 'sf_email'){
//create the email bean
$sea = new SugarEmailAddress();
$sea->addAddress($field_value,true);
//deal with each of the dropdowns
//e.g
}elseif($field_map[$sf_field] == 'sf_lead_status' && $field_value != ''){
$bean_to_load->status = $map_lead_status[$field_value];
//deal with linking records by creating a like_records array to be used later
}elseif(in_array($sf_field, $link_fields) && $field_value != ''){
if( $module_class == 'Task'){
$sb = false;
//get the id for the Account
$sb = get_sugar_bean($field_value, 'Account', true);
if(!empty($sb)) $bean_to_load->task_account_id_c = $sb;
}else{
$sf_mod = trim(str_replace('Converted','',(str_replace('Id','',$sf_field))));
$sb = false;
//get the bean to link the records
$sb = get_sugar_bean($field_value, $sf_mod);
if(!empty($sb)) $link_records[] = $sb;
}
//deal with attachments
}elseif($field_map[$sf_field] == 'sf_document_name' && $field_value != ''){
$bean_to_load->filename = $field_value;
$bean_to_load->document_name = $field_value;
}
//map everything else that doesn't need special processing
//don't map sf_ fields, those are placeholders for something else they are not valid bean fields.
if(isset($field_map[$sf_field]) && !preg_match('/^sf_/',$field_map[$sf_field])){
//echo("{$field_map[$sf_field]}={$field_value}<p>");
$bean_to_load->$field_map[$sf_field]=$field_value;
}
}
}
//set any default values depending on the bean you are saving
if($module_class == 'Meeting') $bean_to_load->status = 'Held';//all meetings in SF are archived, thus Completed
//deal with multi-enum encoding
if(isset($concat_prods_of_int) && !empty($concat_prods_of_int)) $bean_to_load->product_of_interest_c = encodeMultienumValue($concat_prods_of_int);
//check for duplicates
list($duplicates, $dup_id) = checkForDuplicates($bean_to_load);
if($duplicates){
$is_duplicate = '1';
//if it's a duplicate use the dup bean which is the exsting bean to link everything else
// and update what's new
$dup_bean = new $module_class();
$dup_bean->retrieve($dup_id);
//add any new information without overwriting old info
foreach(array_keys($dup_bean->field_name_map) as $field){
if((!isset($dup_bean->$field) || empty($dup_bean->$field)) && (isset($bean_to_load->$field) && !empty($bean_to_load->$field))){
$dup_bean->$field = $bean_to_load->$field;
}
}
$dup_bean->date_modified = $bean_to_load->date_modified;
$dup_bean->modified_user_id = $bean_to_load->modified_user_id; //set the modified by to the user who last modified the SF record
$dup_bean->update_modified_by = false; //prevent the system from overwriting the modified by
$dup_bean->update_date_modified = false;
$dup_bean->save();
unset($bean_to_load);
$sugar_id = $dup_bean->id; //use this exsiting bean to link addresses and related records (below)
}else{
//Save the new bean
//prevent the save process from overriding created by and modified by
$bean_to_load->update_date_modified = false;
$bean_to_load->update_modified_by = false;
$bean_to_load->set_created_by = false;
$bean_to_load->update_date_entered = false;
$bean_to_load->save();
$sugar_id = $bean_to_load->id; //use the new bean to link related records (below)
}
if(isset($sugar_id) && !empty($sugar_id)){
$sugar_bean = new $module_class();
$sugar_bean->retrieve($sugar_id);
// Link the email to the bean
if(isset($sea)){
$mod = $sugar_bean->module_dir;
$sea->save($sugar_id, $mod);
}
//log the SF to Sugar mapping in sfrc_reference
$insert = "INSERT INTO sfrc_reference (sf_id, sugar_id, sf_module, sugar_module, is_duplicate) values ('{$salesforce_id}','{$sugar_id}','{$sfrc_table}','{$module_class}','{$is_duplicate}')";
$db->query($insert);
//link related records that we stored int he temporary array
$link_records = array_filter($link_records);
foreach ($link_records as $related_bean){
if(!empty($related_bean) && !empty($sugar_bean)) link_record_beans($related_bean, $sugar_bean);
}
if($module_class == 'pgdoc_Partnership_Documents'){
//copy the attachment to the appropriate dir with the sugar_id of the documents record
copy($sf_attach_directory.$salesforce_id, 'upload/'.$sugar_id);
}
}else{
//echo('<p>No Sugar ID<p>');
}
//echo('<p>NEXT<p>');
}
return(true);
}
//MAP FIELDS - and process each of the sfrc_ tables as needed
/* Note: in Maps:
pg_ fields are custom Partnership Group fields
sf_ fields are salesforce fields that require manipulation before entering
*/
//Account Map
$account_map = array(
'Id'=>'pg_salesforce_id_c'
,'IsDeleted'=>'deleted'
// IGNORE ALL EMPTY //,'MasterRecordId'=>
,'Name'=>'name'
,'Type'=>'pg_account_type_c'
//etc.... add you mapping here
);
// -- end Account Map
// Contacts Map
$contact_map = array(
'Id'=>'pg_salesforce_id_c'
,'IsDeleted'=>'deleted'
,'AccountId'=>'sf_account_id'
//etc.... add you mapping here
);
// end Contact Map
//Leads Map
//Opportunity Map
//continue maps as needed
//After all the definitions, start running things.
//Load Accounts
import_data('sfrc_Account', $account_map, 'Account');
echo("Accounts Done <p>");
//Load Contacts
import_data('sfrc_Contact', $contact_map, 'Contact');
// For Contacts we need to
// Need to loop around once again to get the ReportsTo links in
$link_contacts =<<<LINKCONT
select s1.sugar_id as contact_id, s2.sugar_id as reports_to_id from sfrc_Contact sc
join sfrc_reference s1 on s1.sf_id = sc.Id
join sfrc_reference s2 on s2.sf_id = sc.ReportsToId
where ReportsToId is not null
LINKCONT;
$result=$db->query($link_contacts);
while($row=$db->fetchByAssoc($result)){
$contact = new Contact();
$contact->retrieve($row['contact_id']);
$contact->reports_to_id = $row['reports_to_id'];
$contact->update_date_modified = false;
$contact->update_modified_by = false;
$contact->set_created_by = false;
$contact->update_date_entered = false;
$contact->save();
//setTeamsAndBusinessRegion('Contact', $contact->id);
}
echo("Contacts Done <p>");
//Load Opportunities
import_data('sfrc_Opportunity', $opportunity_map, 'Opportunity');
$now = gmdate("Y-m-d H:i:s");
//loop around once more to
// link contacts to the opportunities
$link_contacts =<<<SELECT
SELECT Role AS contact_role
,opp.sugar_id as opp_id
,con.sugar_id as contact_id
FROM sfrc_OpportunityContactRole
,sfrc_reference opp
,sfrc_reference con
WHERE con.sf_id = sfrc_OpportunityContactRole.ContactId and con.sugar_module = 'Contact'
AND opp.sf_id = sfrc_OpportunityContactRole.OpportunityId and opp.sugar_module = 'Opportunity'
AND IsDeleted = 0
SELECT;
$result=$db->query($link_contacts);
while($row=$db->fetchByAssoc($result)){
if(!empty($row['contact_id']) && !empty($row['opp_id'])){
$contact_id = $row['contact_id'];
$opp_id = $row['opp_id'];
$cont = new Contact();
$cont->retrieve($contact_id);
$opp = new Opportunity();
$opp->retrieve($opp_id);
if(!empty($cont) && !empty($opp)) link_record_beans($cont, $opp);
}
}
echo("Opportunities Done <p>");
//Load Leads -- must come after Opps for links to work correctly, Leads have OppId.
import_data('sfrc_Lead', $lead_map, 'Lead');
echo("Leads Done <p>");
//Load Tasks
import_data('sfrc_Task', $task_map, 'Task');
echo("Tasks Done <p>");
//Load Notes
import_data('sfrc_Note', $note_map, 'Note');
echo("Notes Done<p>");
//Load Event/Meetings
import_data('sfrc_Event', $event_map, 'Meeting');
echo("Event/Meetings Done<p>");
//Load Attachments/PGDocuments
include_once('modules/pgdoc_Partnership_Documents/pgdoc_Partnership_Documents.php');
import_data('sfrc_Attachment',$attachment_map, 'pgdoc_Partnership_Documents');
echo("Attachments/PGDocuments Done<p>");
?>
5 min read