Just providing a heavily simplified script that was shared with me by [color=#ff0000]pgr[/color]. Thought the Community could make some use out of it. I’ve added a bunch of comments to kind of guide people along. Some isn’t commented as I don’t have a bunch of time. Feel free to add to it and repost.
Zip File attached.
Script Creates new record if none exists, updates record if does exist (retrieves record without knowing ID), and adds a record to a related module.
Big thanks to all that take the time to help out in the community 
<?php
if (!defined('sugarEntry') || !sugarEntry) die ('Not a Valid Entry Point!');
$date = new DateTime();
echo '<H2>Import Started</h2>';
echo $date->format('r').'<br>';
echo '-------------------------------------------------------------------------------------------------------------------------------------<br>';
require_once("include/utils/sugar_file_utils.php");
MemberImportJob();
DuesImportJob();
die();
function time_elapsed()
{
static $first = null;
static $previous = null;
$now = microtime(true);
if ($first == null) $first = $now;
if ($previous != null)
echo("<br /><br /><strong>---------------------------------------------------------------------------------------------------------</strong><br />");
echo '--- Partial ' . round(($now - $previous), 2) . ', Total ' . round(($now - $first), 2) . ' ---<br />'; // 109s
echo("<strong>---------------------------------------------------------------------------------------------------------</strong><br />");
$ret = round(($now - $previous), 2);
$previous = $now;
return $ret;
}
function myLog ($str2log)
{
file_put_contents('./zlog_'.date("j.n.Y").'.txt', date("H:i:s", time())." ".$str2log.PHP_EOL, FILE_AND);
}
function calcDelta($a1, $a2)
{
//combine into a nice associative array:
$delta=Array();
foreach ($a1 as $key=>$value)
{
if ($a1[$key] != $a2->$key)
$delta[] = array($key => ("Was ". $a1[$key]. ", became " . $a2->$key));
}
if (empty($a1)) $delta[] = array("a1" => ("Was empty"));
if (empty($a2)) $delta[] = array("a2" => ("Was empty"));
return $delta;
}
require_once("include/utils/sugar_file_utils.php");
//Create a new record if one isn't found
//$person = Database row being created EG.... $person->your_field_name
//$record = Field data from CSV
//$employeeID is proprietary and prolly unneeded for others
function fillPerson($record, &$person, $employeeID)
{
if ($person->last_name == "" && $record["Last Name"] !== "") {
echo("<br /><br /><strong style='color:red;'>Record Not Found!</strong><br /><br />");
echo("Creating New Member Record for " . $record["LAST NAME"] . " " . $record["FIRST NAME"] . "<br /><br />");
$employeeID = $record["EMP ID"];
$employeeIDLeadingZeros = str_pad($employeeID, 12, '0', STR_PAD_LEFT); //***********Formats the employee ID to be 12 characters long with leading zeros if ID is under 12 characters
echo("Add Leading Zeros to Employee ID: " . $employeeID . "<br /><br />");
echo("New Employee ID Formatted to Standardized Format: " . $employeeIDLeadingZeros . "<br /><br />");
$person->first_name = $record["FIRST NAME"];
$person->last_name = $record["LAST NAME"];
$person->primary_address_street = $record["ADDRESS 1"];
$person->primary_address_street_2_c = $record["ADDRESS 2"];
$person->primary_address_city = $record["CITY"];
$person->primary_address_state = $record["STATE"];
$person->primary_address_postalcode = $record["ZIP"];
$person->lcl_affiliate_number = $record["AFFIlIATE"];
$person->lcl_employee_id = $employeeIDLeadingZeros;
$recordname = (($record["LAST NAME"] . " " . $record["FIRST NAME"]));
$person->full_name_import = $recordname;
$person->name = $person->first_name . " " . $person->last_name;
$person->last_ppe_date = $record[""];
if ($record["EMP ID"] == ""){$person->lcl_employee_id == "000000000000";}
//if SSN is missing then fill the field with X's otherwise fill SSN from CSV
if ($record["SSN"] == "")
{
$person->ssn = "XXX-XX-XXXX";
} else {
$person->ssn = $record["SSN"];
}
$person->lcl_birthdate = $record["DOB"];
$person->lcl_data_source_c = "Uploaded";
}
return calcDelta($person->fetched_row, $person);
}
//If record is found update fields
function fillFoundrecord($record, &$person, $employeeID)
{
$employeeID = $record["EMP ID"];
$employeeIDLeadingZeros = str_pad($employeeID, 12, '0', STR_PAD_LEFT);
//$person->lcl_employee_id = $employeeIDLeadingZeros;
//('first_name' => $record["FIRST NAME"], 'last_name' => $record["LAST NAME"], 'lcl_affiliate_number' => $record["AFFIlIATE"],
if($person->last_name !== "" && $record["LAST NAME"] !== "" && $person->lcl_employee_id == $employeeIDLeadingZeros) //check member by employee id
{
echo("<br /><br /><strong style='color:red;'>Record Found for: " . $person->last_name . ", " . $person->first_name . "</strong><br /><br />");
echo("Updating Member Record for " . $record["LAST NAME"] . " " . $record["FIRST NAME"] . "<br /><br />");
//$person->lcl_employee_id = $employeeIDLeadingZeros;
if ($record["SSN"] = "") {
$person->ssn = "XXX-XX-XXXX";
} else {
$person->ssn = $record["SSN"];
}
echo("Adding Leading Zeros to Employee ID: " . $employeeID . "<br /><br />");
echo("New Employee ID Formatted to Standardized Format: " . $employeeIDLeadingZeros . "<br /><br />");
$person->first_name = $record["FIRST NAME"];
$recordname = (($record["LAST NAME"] . " " . $record["FIRST NAME"]));
$person->full_name_import = $recordname;
$recordnameV = (($record["LAST NAME"] . ", " . $record["FIRST NAME"]));
$person->name = $recordnameV;
$person->last_ppe_date = $record[""];
$Address1 = $person->primary_address_street;
$Address2 = $person->primary_address_street_2_c;
$City = $person->primary_address_city = $record;
$State = $person->primary_address_state = $record;
$Zip = $person->primary_address_postalcode = $record;
if ($record["EMP ID"] == ""){$person->lcl_employee_id == "000000000000";}
$person->lcl_birthdate = $record["DOB"];
$person->lcl_data_source_c = "Uploaded";
if($Address1 == "" && $record["ADDRESS 1"] !== "")
{
$person->primary_address_street = $record["ADDRESS 1"];
$person->primary_address_street_2_c = $record["ADDRESS 2"];
$person->primary_address_city = $record["CITY"];
$person->primary_address_state = $record["STATE"];
$person->primary_address_postalcode = $record["ZIP"];
}
echo "Record Updated Using Employee ID!";
}
return calcDelta($person->fetched_row, $person);
}
function fillDues($record, &$person, $employeeID)
{
if ($record["EMP ID"] == ""){$person->lcl_employee_id == "000000000000"; $person->dues_emp_id == "000000000000";}
//if($person->lcl_employee_id == $record["EMP ID"]){
echo("***********************************************************************************************************************");
echo("<br /><br /><br />Creating New DUES Record for " . $record["LAST NAME"] . ", " . $record["FIRST NAME"] . "<br /><br />");
echo("Add Leading Zeros to Employee ID: " . $employeeID . "<br /><br />");
echo("New Employee ID Formatted to Standardized Format: " . $employeeIDLeadingZeros . "<br /><br />");
$person->dues_first_name = $record["FIRST NAME"];
// $person->first_name = $record["FIRST NAME"];
$person->dues_last_name = $record["LAST NAME"];
$person->dues_affiliate_number = $record["AFFIlIATE"];
$person->dues_emp_id = $record["EMP ID"];
$recordname = (($record["LAST NAME"] . " " . $record["FIRST NAME"]));
$person->dues_full_name = $recordname;
$person->name = (($record["LAST NAME"] . ", " . $record["FIRST NAME"]));
$person->ppe_date = $record[""];
$person->dues_amount = $record["DUES AMT"];
$person->dues_not_taken = $record["DUES NOT TAKEN"];
$person->lcl_member_status = "Active";
if($Address1 == "")
{
$person->dues_address_1 = $record["ADDRESS 1"];
$person->dues_address_2 = $record["ADDRESS 2"];
$person->dues_city = $record["CITY"];
$person->dues_state = $record["STATE"];
$person->dues_zipcode = $record["ZIP"];
}
if ($record["SSN"] = "") {
$person->dues_ssn = "XXX-XX-XXXX";
} else {
$person->dues_ssn = $record["SSN"];
}
$person->dues_dob = $record["DOB"];
//}
return calcDelta($person->fetched_row, $person);
}
function MemberImportJob()
{
try
{
echo("Member Check/Update/Import Started Using Employee ID as Hard Match");
$GLOBALS['log']->info('Member Check/Update/Import Import');
$config = new Configurator();
$config->loadConfig();
$xmlDataDir = 'custom/wimporter/eidimport'; //$config->config['Importer_DataFilePath'];
$GLOBALS['log']->info("Member Check/Update/Import: Scanning XML Data dir $xmlDataDir...");
echo("<h3> Import: Scanning XML Data dir $xmlDataDir...<br /></h3>");
$directoryContent = scandir($xmlDataDir);
$GLOBALS['log']->info("Member Check/Update/Import: Scanning XML Data dir $xmlDataDir... [Found " . (count($directoryContent)-2) . " files]");
echo("<h3> Import: Scanning XML Data dir $xmlDataDir... [Found " . (count($directoryContent)-2) . " files]</h3>");
foreach ($directoryContent as $itemFile)
{
// var_dump($itemfile);
if (is_dir($xmlDataDir . DIRECTORY_SEPARATOR . $itemFile)) continue;
if (strcasecmp(substr($itemFile, -4), ".csv") != 0) continue;
$GLOBALS['log']->info("Member Check/Update/Import: Processing $itemFile file...");
myLog("---------------------------------------------------");
myLog("Member Check/Update/Import: Processing $itemFile file...");
myLog("----------------------------------------------------");
echo("<br /><br /><strong>---------------------------------------------------------------</strong><br />");
echo("<strong>Member Check/Update/Import: Processing $itemFile file...</strong><br />");
echo("<strong>---------------------------------------------------------------</strong><br /><br />");
$oFile = fopen($xmlDataDir . DIRECTORY_SEPARATOR . $itemFile, 'r');
if ($oFile !== FALSE)
{
$header = NULL;
$data = Array();
while (($data[] = fgetcsv($oFile, 90000, ',')) !== FALSE) { if ($data[0] == NULL){continue;} }
//if ($data[0] == NULL) //test this to skip empty lines
// continue; // end test
//if (count(array_filter($csv)) == 0) {
echo("<strong>File opened..... </strong><br /> <br />");
fclose($oFile);
//combine into a nice associative array:
$arow=Array();
echo('<strong>Building Member Row Data from Roster' . $itemFile . '<br /><br />');
$fields = array_shift($data);
echo('Building Header Fields Array from Roster as shown below: <br /><br />');
echo implode(", ", $fields) . "</strong><br /><br />\n";
foreach ($data as $i=>$arow)
{
$GLOBALS['log']->info("Member Check/Update/Import: array_combine " . $i);
if (is_array($arow)) {
$data[$i] = array_combine($fields, $arow);}
}
unset($arow); // **********************************************! ! ! !! ! ! ! ! ! !! !
$num = count($data);
echo('<br /><strong>Build Combined Array of Roster Data to be Imported Complete. Entries to be imported are shown below: </strong> <br /><br />');
for ($row=0; $row < $num - 1; $row++)
{ // normal bounds: from 0 to $num
echo "<br /><br /><strong>Filename: $itemFile | Roster Import, Row " . ($row + 1) . ":</strong><br />\n";
$GLOBALS['log']->info(" Import: Importing " . $data[$row]['FULL NAME']);
echo("<strong>Importing/Updating Roster Row #: ". ($row + 1) . "<br />" . "Local Number " . $data[$row]["AFFIlIATE"] . "<br />" . "Employee: " . $data[$row]["FULL NAME"] . "</strong><br /><br />");
echo "File Row Data: <br /><br />";
echo implode(", ", $data[$row]);
$employeeIDfunc = $data[$row]["EMP ID"];
$employeeIDLeadingZerosfunc = str_pad($employeeIDfunc, 12, '0', STR_PAD_LEFT);
$Member = BeanFactory::getBean("locte_Membership");
//Retrive by available data. I have 3 files for different import types. Some CSV's are imported by SSN, some by EmployeeID, Some have neither
//and a custom id is built in an excel script. I you can uncomment array objects below and match records based on more detailed criteria.
$Member=$Member->retrieve_by_string_fields(array(
//'last_name' => $data[$row]["LAST NAME"],
//'first_name' => $data[$row]["FIRST NAME"],
'lcl_affiliate_number' => $data[$row]["AFFIlIATE"],
'lcl_employee_id' => $employeeIDLeadingZerosfunc,
//'ssn' => $data[$row]["SSN"]
)
);
$MemberID = $Member->id;
if (is_null($Member)) { //check to see if record exists in database
$Member = BeanFactory::newBean('locte_Membership');
$delta = fillPerson($data[$row], $Member, "EMP ID"); // if it doesnt then create it
if(count($delta))
{
$Member_id = $Member->save();
echo("New record Saved!");
}
} else {
var_dump($Member->id);
$delta = fillFoundrecord($data[$row], $Member, "EMP ID"); //if it does retrieve the record ID and update the fields specified in the fillFoundrecord()
$Member_id = $Member->save();
}
myLog("Importing done in " . round(time_elapsed(),2) . " seconds.<br /><br />");
echo("<br /><br /><strong>****************************************************************************************************************************************************************</strong>");
echo("<br /><br /><strong>****************************************************************************************************************************************************************</strong>");
unset($data[$row]);
}
}
}
$GLOBALS['log']->info('End: Member Update/Import');
myLog('End: Import');
echo("<strong><h3>---------------------------------------------------------------------------------------------------------</h3></strong>");
echo("<p>End: Member/Update Import<br />");
echo("<strong><h3>---------------------------------------------------------------------------------------------------------</h3></strong>");
return true;
} catch (Exception $e)
{
$GLOBALS['log']->fatal(" Import: Exception " . $e->getMessage());
myLog(" Import: Exception " . $e->getMessage());
echo '\n\nCaught exception: ', $e->getMessage(), "\n";
return false;
}
}
function DuesImportJob()
{
try
{
echo(" Roster Dues Import Started Using Employee ID as Hard Match");
$GLOBALS['log']->info(' Import');
$config = new Configurator();
$config->loadConfig();
//$xmlDataDir is the directory that the files to be uploaded are stored in and the script iterates through each file until import is complete or error is encountered.
$xmlDataDir = 'custom/wimporter/eidimport'; //$config->config['Importer_DataFilePath'];
$GLOBALS['log']->info(" Roster Dues Import: Scanning XML Data dir $xmlDataDir...");
echo("<h3> Roster Dues Import: Scanning XML Data dir $xmlDataDir...<br /></h3>");
$directoryContent = scandir($xmlDataDir);
$GLOBALS['log']->info(" Import: Scanning XML Data dir $xmlDataDir... [Found " . (count($directoryContent)-2) . " files]");
echo("<h3> Roster Dues Import: Scanning XML Data dir $xmlDataDir... [Found " . (count($directoryContent)-2) . " files]</h3>");
foreach ($directoryContent as $itemFile)
{
if (is_dir($xmlDataDir . DIRECTORY_SEPARATOR . $itemFile)) continue;
if (strcasecmp(substr($itemFile, -4), ".csv") != 0) continue;
$GLOBALS['log']->info(" Import: Processing $itemFile file...");
myLog("---------------------------------------------------");
myLog(" Import: Processing $itemFile file...");
myLog("----------------------------------------------------");
echo("<br /><br /><strong>---------------------------------------------------------------</strong><br />");
echo("<strong> Roster Dues Import: Processing $itemFile file...</strong><br />");
echo("<strong>---------------------------------------------------------------</strong><br /><br />");
$oFile = fopen($xmlDataDir . DIRECTORY_SEPARATOR . $itemFile, 'r');//open directory and begin going through files
if ($oFile !== FALSE) //files still exist, keep going
{
$header = NULL;
$data = Array();
while (($data[] = fgetcsv($oFile, 0, ',')) !== FALSE) { }
echo('<strong>File opened..... </strong><br /> <br />');
fclose($oFile);
//combine into a nice associative array:
$arow=Array();
echo('<strong>Building Array of Row Data from CSV File' . $itemFile . '<br /><br />');
$fields = array_shift($data);
echo('Building Header Fields Array from Roster as shown below: <br /><br />');
echo implode(", ", $fields) . "</strong><br /><br />\n";
foreach ($data as $i=>$arow)
{
$GLOBALS['log']->info(" Import: array_combine " . $i);
if (is_array($arow)) {
$data[$i] = array_combine($fields, $arow);}
//var_dump($fields); //used for testing to see if data is populating into the array
//var_dump($arow);
}
unset($arow); // **********************************************! ! ! !! ! ! ! ! ! !! !
$num = count($data);
echo('<br /><strong>Build Full Array of Roster to be Imported Complete. Entries to be imported are shown below: </strong> <br /><br />');
for ($row=0; $row < $num - 1; $row++)
{ // normal bounds: from 0 to $num
//$num is the number of lines including header in csv file
echo "<br /><br /><strong>Filename: $itemFile | Roster Import, Row " . ($row + 1) . ":</strong><br />\n";
$GLOBALS['log']->info(" Import: Importing " . $data[$row]['FULL NAME']);
echo("<strong>Importing Roster Row #: ". ($row + 1) . "<br />" . "Local Number " . $data[$row]["AFFIlIATE"] . "<br />" . "Employee: " . $data[$row]["FULL NAME"] . "</strong><br /><br />");
echo "File Row Data: <br /><br />";
echo implode(", ", $data[$row]);
$Member = BeanFactory::getBean("locte_Membership");
$Member=$Member->retrieve_by_string_fields(array(
//'last_name' => $data[$row]["LAST NAME"],
//'first_name' => $data[$row]["FIRST NAME"],
'lcl_affiliate_number' => $data[$row]["AFFIlIATE"],
'lcl_employee_id' => $data[$row]["EMP ID"],
//'ssn' => $data[$row]["SSN"]
)
);
$MemberID = $Member->id;
var_dump($Member->id);
$Dues = BeanFactory::newBean('adues_ppe');
$delta = fillDues($data[$row], $Dues, "EMP ID");
if(count($delta))
{
$Dues_id = $Dues->save();
}
$Member->load_relationship('locte_membership_adues_ppe_1'); //relate to data entered in fillperson() and add record to related module
$newrelMember = $Member->locte_membership_adues_ppe_1->add($Dues);
myLog("Importing done in " . round(time_elapsed(),2) . " seconds.<br /><br />");
echo("<br /><br /><strong>****************************************************************************************************************************************************************</strong>");
echo("<br /><br /><strong>****************************************************************************************************************************************************************</strong>");
unset($data[$row]);
unset($newrelMember);
}
}
}
$GLOBALS['log']->info('End: Import');
myLog('End: Dues Import');
echo("<strong><h3>---------------------------------------------------------------------------------------------------------</h3></strong>");
echo("<p>End: Dues Import<br />");
echo("<strong><h3>---------------------------------------------------------------------------------------------------------</h3></strong>");
return true;
} catch (Exception $e)
{
$GLOBALS['log']->fatal(" Import: Exception " . $e->getMessage());
myLog(" Import: Exception " . $e->getMessage());
echo '\n\nCaught exception: ', $e->getMessage(), "\n";
return false;
}
echo(" Import Complete!");
}
$files = glob('custom/wimporter/eidimport/*.csv'); // get all file names
foreach($files as $file){ // iterate files
if(is_file($file))
unlink($file); // delete file