Sample Import_script for SuiteCRM, CSV to beans and relationships, and security groups

Import_script.php

<?php
//xdebug_break();
if (!defined('sugarEntry') || !sugarEntry) die ('Not a Valid Entry Point!');


$date= new DateTime();
//chdir('/opt/bitnami/apps/suitecrm/htdocs/');
echo 'School Importer started at ';
echo $date->format('r').'<br>';
echo '-------------------------------------------------------------------------------------------------------------------------------------<br>';

require_once("include/utils/sugar_file_utils.php");

SchoolImporterJobs();
die();

function time_elapsed()
{
	static $first = null;
	static $previous = null;

	$now = microtime(true);
	if ($first == null) $first = $now;
	if ($previous != null)
	    echo '--- Partial ' . round(($now - $previous), 2) . ', Total ' . round(($now - $first), 2) . ' ---';  // 109s

    $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_APPEND);

}

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));
	}
	//$num = count($data);

	if (empty($a1)) $delta[] = array("a1" => ("Was empty"));
	if (empty($a2)) $delta[] = array("a2" => ("Was empty"));

	return $delta;
}

function fillPerson($record, &$person, $prefix)
{
	require('custom/School/SchoolImportclassesTable.php');


	// $record is what is being imported from CSV
	// $person is the bean about to be filled and going into the SuitCRM DB. It may be new or not, depending on whether it exists in the DB previously.
	// $prefix is "Student: ", "Father: ", "Mother: " ou "EE: " and it's part of the field names to import on the CSV, for example: "Father: Address"

	// name: only updates if not existant yet, because it's the key we use for search, and because names 
	// in SuiteCRM are more complex, with parts
	if ($person->full_name_c == "") {
		$recordname = trim(($record[$prefix . "Name"] != "") ? $record[$prefix . "Name"] : "[To-be-filled]");
		$recordname = str_replace("  ", " ", $recordname);
		$parts = explode(" ", $recordname);
		$person->last_name = array_pop($parts);
		$person->first_name = $parts[0];
		$person->name = $person->first_name . " " . $person->last_name;
		$person->full_name_c = $record[$prefix . "Name"]; // custom field created in Studio
	}
	$datanasc = DateTime::createFromFormat('!d/m/Y', $record[$prefix . "Birthdate"]);
	//$datasnasc->setTime(0, 0);
	$person->birthdate = ($datanasc == false) ? "" : $datanasc->format('Y-m-d');
	$person->email1 = $record[$prefix . "Email"];
	$person->phone_home = $record[$prefix . "Telephone"];
	$person->phone_mobile = $record[$prefix . "Mobile"];
	$person->title = ""; //Dr.";

	if (true || ($person->primary_address_street == "")) {
		$Address  = $record[$prefix . "Address"];
		$codpostal = $record[$prefix . "Codigo Postal"];
		$nporta  = $record[$prefix . "Numero de Porta"];
		$andar   = $record[$prefix . "Andar"];
		$loc   =   $record[$prefix . "Localidade"];

		if ($nporta != "")
			if (!(strpos($Address, $nporta) && strpos($Address, $andar))) {
				$nporta = str_replace('nº ','', $nporta);
				$nporta = str_replace('n. ','', $nporta);
				$nporta = str_replace('nº','', $nporta);
				$nporta = str_replace('n.','', $nporta);
				$nporta = rtrim($nporta, ' ,-.');
				$Address = $Address . ', nº ' . $nporta . (($andar != "") ? (', ' . $andar) : "");
			}
		$Address = str_replace('  ', ' ', $Address);
		$Address = str_replace(',,', ',', $Address);
		$Address = str_replace(', ,', ', ', $Address);
		$Address = rtrim(ltrim($Address));

		// replace Localidade only if at end of string codpostal:
 		if (substr(strtoupper($codpostal),-strlen($loc))===strtoupper($loc))
			$codpostal = rtrim(substr($codpostal, 0, strlen($codpostal)-strlen($loc)));
		// replace Localidade only if at end of string Address:
		if (substr(strtoupper($Address),-strlen($loc))===strtoupper($loc))
			$Address = rtrim(substr($Address, 0, strlen($Address)-strlen($loc)));

		if ($codpostal != "") {
			$Address = str_replace($codpostal, '', $Address); // algumas Addresss incluem codpostal...
			$person->primary_address_postalcode = $codpostal;
		}
		$person->primary_address_street = $Address;
		$person->primary_address_city = ucwords(strtolower($loc));  // converts to title case
		$person->primary_address_country = ""; // $record[$prefix . "Fathers"];
	}

	$sex = $record[$prefix . "Sexo"];
	if ($sex == "Male") $person->sex_c = 'M';
	if ($sex == "Female")  $person->sex_c = 'F';
	if ($prefix == "Father: ")  $person->sex_c = 'M';
	if ($prefix == "Mother: ") $person->sex_c = 'F';

	$num_int =$record[$prefix . "Numero Interno"];
	if ($num_int != "") {
        $person->photo = $num_int . "_photo.jpg";
        //$zing_file = 'upload/Fotos/' . $person->photo;
        //$filename = 'index.php';
        //$file_path = dirname($filename); //. DIRECTORY_SEPARATOR . "{$filename}";
        //clearstatcache();
        //if (count(glob('upload/Fotos/'. $person->photo))==1) {
        if (file_exists('upload/Fotos/' . $person->photo)) {
			copy('upload/Fotos/' . $person->photo, 'upload/' . $person->id . '_photo');
        }
	}

	if ($prefix == "Student: ") {
		$class = $record["Student: class"];
		$college = str_replace("School ", "", $record["Student: Escola"]);
		if (is_array($classes[$college][$class])) {
			// 	0 "Anos", 1: "Ano", 2: "class", 3: "Finalistas", 5: "Curso"),
			$person->grade_c = $classes[$college][$class][1];
			$person->class_c = $classes[$college][$class][2];
			$person->classe_c = $classes[$college][$class][3];
		}
        else {
            mylog("Can't find class " . $class . " at ".$college . " for ".$person->full_name_c );

			$person->grade_c = "Alumni";
			//$person->class_c = $classes[$college][$class][2];
			//$person->classe_c = $classes[$college][$class][3];
        }
	}
	// finish by making a complete analysis of what changed:
 	return calcDelta($person->fetched_row, $person);
}


function GetOrCreateSimpleContact ($the_name, $college) {
    if ($the_name != "") {
        $person = BeanFactory::getBean("Contacts");
        $person = $person->retrieve_by_string_fields(array('full_name_c' => $the_name));
        if (is_null($person)) {
            $person = BeanFactory::newBean("Contacts");
            $person->full_name_c = $the_name;
            $person_name = str_replace("  ", " ", $the_name);
            $parts = explode(" ", $person_name);
            $person->last_name = array_pop($parts);
            $person->first_name = $parts[0];
            $person->name = $person->first_name . " " . $person->last_name;
            $person_id = $person->save();
            // add new person to account belonging to his school:
            $account = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name'=> $college));
            $person->load_relationship('accounts');
            $person->accounts->add($account->id);

            // now set Security so accesses are granted to appropriate users:
            $securityTeam = new SecurityGroup();
            $securityTeam->retrieve_by_string_fields(array('name' => $college ));
            if ( $securityTeam->id == null)
                $GLOBALS['log']->fatal("Group with the name '" . array('name' => $college ) . "' wasn't found!");
            $person->load_relationship('SecurityGroups');
            $person->SecurityGroups->add($securityTeam->id);
            return $person;
        }
        return $person;
    }
    return null;
}

/* ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************
 * ****************************************************************************************************/

//array_push($job_strings, 'SchoolImporterJobs');
function SchoolImporterJobs()
{
try{
	time_elapsed();
	$GLOBALS['log']->info('Running: SchoolImporterJob');
	$config = new Configurator();
	$config->loadConfig();
	$csvDataDir =  'custom/School/ToImport'; //$config->config['SchoolImporter_DataFilePath'];

	$GLOBALS['log']->info("SchoolImporterJob: Scanning CSV Data dir $csvDataDir...");

	$directoryContent = scandir($csvDataDir);

	$GLOBALS['log']->info("SchoolImporterJob: Scanning CSV Data dir $csvDataDir... [Found " . count($directoryContent) . " files]");

	foreach ($directoryContent as $itemFile) {
		if (is_dir($csvDataDir . DIRECTORY_SEPARATOR . $itemFile)) continue;
		if (strcasecmp(substr($itemFile, -4), ".csv") != 0) continue;
		$GLOBALS['log']->info("SchoolImporterJob: Processing $itemFile file...");
		myLog("---------------------------------------------------");
		myLog("SchoolImporterJob: Processing $itemFile file...");
		myLog("----------------------------------------------------");
		$oFile = fopen($csvDataDir . DIRECTORY_SEPARATOR . $itemFile, 'r');

		if ($oFile !== FALSE) {
			// read entire file at once:
			// expected separator is ",", expected encoding is UTF-8 without BOM (BOM is 3 weird characters in beginning of file)
			while (($data[] = fgetcsv($oFile, 3000, ',')) !== FALSE) { }
			fclose($oFile);

			//combine into a nice associative array:
			$arow=Array();
			$csvFields = array_shift($data);
			foreach ($data as $i=>$arow) {
				//$GLOBALS['log']->info("SchoolImporterJob: array_combine " . $i);
				$data[$i] = array_combine($csvFields, $arow);
			}
            unset($arow); // **********************************************! ! ! !! ! ! ! ! ! !! !

            //$myUser =  BeanFactory::getBean('Users')->retrieve_by_string_fields(array('first_name' => 'MyName'));

            $SchoolA = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'SchoolA'));
            $SchoolB = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'SchoolB'));
            $SchoolC = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'SchoolC'));
            $SchoolD = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'SchoolD'));
            $SchoolAAlumni = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'A.Alumni'));
            $SchoolBAlumni = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'B.Alumni'));
            $SchoolCAlumni = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'C.Alumni'));
            $SchoolDAlumni = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => 'D.Alumni'));
            $cgi_accounts=Array(
                "SchoolA" => $SchoolA,
                "SchoolB" => $SchoolB,
                "SchoolC" => $SchoolC,
                "SchoolD" => $SchoolD,
                "A.Alumni" => $SchoolAAlumni,
                "B.Alumni" => $SchoolBAlumni,
                "C.Alumni" => $SchoolCAlumni,
                "D.Alumni" => $SchoolDAlumni,
            );

            $num = count($data);
	        echo "<p> $num lines in file $row: <br /></p>\n";
			// echo implode(", ", $csvFields) . "<br /><br />\n";

	        for ($row=0; $row < $num ; $row++) {   // normal bounds: from 0 to $num
                $limitnames = ""; 
                $limitclasses = "";  
                $limitcollege = "";

                if (($limitnames!="") and (substr($data[$row]["Student: Name"], - strlen($limitnames)) != $limitnames)) {
					$GLOBALS['log']->info("SchoolImporterJob: Skipping " . $data[$row]["Student: Name"]);
	        		myLog("Skipping " . $data[$row]["Student: Name"]);
	        		continue;
	        	}

                $college = str_replace("School ", "", $data[$row]["Student: Escola"]);
	        	if (($limitcollege!="") and ($college != $limitcollege)) {
					$GLOBALS['log']->info("SchoolImporterJob: Skipping " . $college);
					myLog("Skipping by limitcollege " . $college. " for " . $data[$row]["Student: Name"]);
	        		continue;
	        	}

	        	if (($limitclasses!="") and (substr($data[$row]["Student: class"], - strlen($limitclasses)) != $limitclasses)) {
					$GLOBALS['log']->info("SchoolImporterJob: Skipping " . $data[$row]["Student: class"]);
					myLog("Skipping by limitclasses " . $data[$row]["Student: class"] . " for " . $data[$row]["Student: Name"]);
	        		continue;
	        	}

                if ($data[$row]["Student: Numero Interno"] == "") {
					$GLOBALS['log']->info("SchoolImporterJob: Skipping empty Numero Interno for " . $data[$row]["Student: Name"]);
	        		myLog("Skipping empty Numero Interno for " . $data[$row]["Student: Name"]);
	        		continue;
	        	}

                $GLOBALS['log']->info("SchoolImporterJob: Importing " . $data[$row]["Student: Name"]);
                myLog("Importing #". $row. " " . $data[$row]["Student: Name"]);
                // 	        	echo "<table>\n";
// 	        	foreach ($csvFields as $aField) {
// 	        		echo "<tr><td>" . $aField . "</td><td>" . $data[$row][$aField] . "</td><td>" . $data[$row+1][$aField] . "</td><td>" . $data[$row+2][$aField] . "</td></tr>\n";
// 	        	}
// 	        	echo "</table>\n";
// 	        	echo implode(", ", $data[$row]) . "<br /><br />\n";

	        	//in input file, each line (record) includes fields for Student, mother, Father and ee

	        	$Student = BeanFactory::getBean("Contacts");
	        	$Student=$Student->retrieve_by_string_fields(array('full_name_c' => $data[$row]["Student: Name"]));
	        	if (is_null($Student))
	        		$Student = BeanFactory::newBean("Contacts");
				$delta = fillPerson($data[$row], $Student, "Student: ");

                // tutores, with Account college, linked as relationships:
                // dt = Director de class, filled in as direct reports
                $tutor = GetOrCreateSimpleContact($data[$row]["Student: tutor"], $college);
                $dt = GetOrCreateSimpleContact($data[$row]["Student: Diretor de class"], $college);
                $extra_change = false;
				if ($Student->reports_to_id != $dt->id) {
                    $Student->reports_to_id = $dt->id;
                    $extra_change = true;
                }
                if (count($delta) || $extra_change) {
					$Student_id = $Student->save();
                }

	        	$Father = BeanFactory::getBean("Contacts");
				$Father=$Father->retrieve_by_string_fields(array('full_name_c' => $data[$row]["Father: Name"]));
	        	if (is_null($Father))
					$Father = BeanFactory::newBean("Contacts");
				$delta = fillPerson($data[$row], $Father, "Father: ");
				if (count($delta))
					$Father_id = $Father->save();

				$mother = BeanFactory::getBean("Contacts");
	        	$mother=$mother->retrieve_by_string_fields(array('full_name_c' => $data[$row]["Mother: Name"]));
	        	if (is_null($mother))
					$mother = BeanFactory::newBean("Contacts");
				$delta = fillPerson($data[$row], $mother, "Mother: "); // Mother é Mother...
				if (count($delta))
				    $mother_id = $mother->save();

				// Records have been saved: from this point on, only work on relationships:
				// primaryEducator:
				$Father_ee = 0;
				$mother_ee = 0;
				if ($Father->full_name_c == $data[$row]["EE: Name"])
					$Father_ee =1;
				if ($mother->full_name_c == $data[$row]["EE: Name"])
					$mother_ee = 1;

				$Student->load_relationship('contacts_contacts_1');
				$newrelFather = $Student->contacts_contacts_1->add($Father, array('reltype' => 'Father', 'primaryEducator' => $Father_ee));
				$newrelmother = $Student->contacts_contacts_1->add($mother, array('reltype' => 'Mother', 'primaryEducator' => $mother_ee));
                $newreltutor = $Student->contacts_contacts_1->add($tutor, array('reltype' => 'tutor', 'primaryEducator' => 'false'));

                // student gets into account for his/her school / class:
				$grade = substr($college, 0, 1) . "." . $Student->grade_c;
				$account = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name' => $grade));
                if (is_null($account)) {
					$account = BeanFactory::newBean("Accounts");
                    $account->parent_id = $cgi_accounts[$college]->id;
                    $account->name = $grade;
                    $account->save();
                    $securityTeam = new SecurityGroup();
                    $securityTeam->retrieve_by_string_fields(array('name' => $college ));
                    $account->load_relationship('SecurityGroups');
                    $account->SecurityGroups->add($securityTeam->id);
                }

                $Student->load_relationship('accounts');
				$Student->accounts->add($account->id);
                // remove Student from generic accounts like "P.Alumni", "M.Alumni":
                if ($Student->grade_c != "Alumni") {
                    $somereturn =  $Student->accounts->delete($Student->id, $cgi_accounts[substr($college, 0, 1) . ".Alumni"]);
                    myLog('Deleting Alumni account '. (substr($college, 0, 1) . ".Alumni").' returned '.print_r($somereturn));
                }
                // remove Student from generic accounts like "SchoolA", "SchoolB":
                $somereturn =  $Student->accounts->delete($Student->id, $cgi_accounts[$college]);


				// parents might have kids in two schools, so they all go into generic "Fathers" account:
				$account = BeanFactory::getBean('Accounts')->retrieve_by_string_fields(array('name'=> 'Fathers'));
				$Father->load_relationship('accounts');
				$Father->accounts->add($account->id);
				$mother->load_relationship('accounts');
				$mother->accounts->add($account->id);

// now set Security so accesses are granted to appropriate users:
				$securityTeam = new SecurityGroup();
				$securityTeam->retrieve_by_string_fields(array('name' => $college ));
				if ( $securityTeam->id == null)
					$GLOBALS['log']->fatal("Group with the name '" . array('name' => $college ) . "' wasn't found!");
				$Student->load_relationship('SecurityGroups');
				$Student->SecurityGroups->add($securityTeam->id);
				$Father->load_relationship('SecurityGroups');
				$Father->SecurityGroups->add($securityTeam->id);
				$mother->load_relationship('SecurityGroups');
				$mother->SecurityGroups->add($securityTeam->id);
				if (!is_null($tutor)) {
                    $tutor->load_relationship('SecurityGroups');
				    $tutor->SecurityGroups->add($securityTeam->id);
                }

				//$GLOBALS['log']->info("SchoolImporterJob: Importing done in " . round(time_elapsed(),2) . " seconds.");
				myLog("Importing done in " . round(time_elapsed(),2) . " seconds.");
	        	echo "<br /><br />\n";
                unset($data[$row]);
	        }
            // echo print_r($GLOBALS["beanList"]);

		}
	}

	$GLOBALS['log']->info('End: SchoolImporterJob');
	myLog('End: SchoolImporterJob');
	time_elapsed();

	return true;

} catch (Exception $e) {
	$GLOBALS['log']->fatal("SchoolImporterJob: Exception " . $e->getMessage());
	myLog("SchoolImporterJob: Exception " . $e->getMessage());
	echo '\n\nCaught exception: ',  $e->getMessage(), "\n";
    return false;
}
}

/*
			$myfield_defs = $Student->getFieldDefinitions(); // just to help while developing
	        foreach($myfield_defs as $def) echo $def["name"] . "<br />\n";

Fields from SugarBean "Contact":
------------------------------
id
name
date_entered
date_modified
modified_user_id
modified_by_name
created_by
created_by_name
description
deleted
created_by_link
modified_user_link
assigned_user_id
assigned_user_name
assigned_user_link
SecurityGroups
salutation
first_name
last_name
full_name
title
photo
department
do_not_call
phone_home
email
phone_mobile
phone_work
phone_other
phone_fax
email1
email2
invalid_email
email_opt_out
primary_address_street
primary_address_street_2
primary_address_street_3
primary_address_city
primary_address_state
primary_address_postalcode
primary_address_country
alt_address_street
alt_address_street_2
alt_address_street_3
alt_address_city
alt_address_state
alt_address_postalcode
alt_address_country
assistant
assistant_phone
email_addresses_primary
email_addresses
email_addresses_non_primary
email_and_name1
lead_source
account_name
account_id
opportunity_role_fields
opportunity_role_id
opportunity_role
reports_to_id
report_to_name
birthdate
accounts
reports_to_link
opportunities
bugs
calls
cases
direct_reports
emails
documents
leads
meetings
notes
project
project_resource
tasks
tasks_parent
notes_parent
user_sync
camFathergn_id
camFathergn_name
camFathergns
camFathergn_contacts
c_accept_status_fields
m_accept_status_fields
accept_status_id
accept_status_name
prospect_lists
sync_contact
fp_events_contacts
aos_quotes
aos_invoices
aos_contracts
e_invite_status_fields
event_status_name
event_invite_id
e_accept_status_fields
event_accept_status
event_status_id
project_contacts_1
aop_case_updates
joomla_account_id
portal_account_disabled
joomla_account_access
portal_user_type
full_name_c
grade_c
class_c
classe_c
enceduc_c    // not used, we have field in Relationship instead
sex_c
contacts_contacts_1
e_reltype
reltype
rel_id
jjwg_maps_address_c
jjwg_maps_geocode_status_c
jjwg_maps_lat_c
jjwg_maps_lng_c




SQL Commands:
Orphaned contacts_cstm:
SELECT * FROM `contacts_cstm` as t1 LEFT JOIN `contacts` as t2 ON t1.`id_c` = t2.`id` WHERE t2.`id` IS NULL
delete t1 FROM `contacts_cstm` as t1 LEFT JOIN `contacts` as t2 ON t1.`id_c` = t2.`id` WHERE t2.`id` IS NULL

Orphaned contacts_contacts_1_c fro ida side:
SELECT * FROM `contacts_contacts_1_c` as t1 LEFT JOIN `contacts` as t2 ON t1.`contacts_contacts_1contacts_ida` = t2.`id` WHERE t2.`id` IS NULL

Orphaned contacts_contacts_1_c fro idb side:
SELECT * FROM `contacts_contacts_1_c` as t1 LEFT JOIN `contacts` as t2 ON t1.`contacts_contacts_1contacts_idb` = t2.`id` WHERE t2.`id` IS NULL

 */