Создание предварительных контактов(в таблице leads) и их связь с таблицей Email в sugarcrm из Excel или CSV файла.

<?php
class myLead {
//[НАЧАЛО]Настройки
  public static $user = "логин";
  public static $password = "пароль";
  public static $db = "crm";
  public static $leadTable = "leads";//Название таблицы предварительные контакты
  public static $emailTable = "email_addresses";//Таблица в которой хранятся E-mail адреса
  public static $emailPlusLeadTable = "email_addr_bean_rel";//таблица используемая для связи таблицы лидов с таблицей E-mail адресов
  public static $emailTmpTable = "emails_add_tmp_table";
  public static $userAr = array(//если массив содержит значения Id соответствующих полей, то они будут установлены если соответствующее поле не было передано методу в массиве, если какое либо из значений не задано здесь или в массиве оно устанавливается из created_by
    "modified_user_id"=>false,//пример для установки значения "a664b594-0bb6-1ecb-def7-5673ba48c82b"
    "created_by"=>"a664b594-0bb6-1ecb-def7-5673ba48c82b",
    "assigned_user_id"=>"ddfe99b9-4945-663a-1c9b-5f884eedbaa3"
  );
//[КОНЕЦ]Настройки

public static $pdo = false;
  public static $chackedEmailAr = array();
  public static $lastEmailExist = false;

public static function addLead($array=false) {
    if (is_array($array)) {
      $allowedFields = array(
        "date_entered",//2021-04-12 11:54:38
        "date_modified",//2021-04-12 11:54:38
        "modified_user_id",//a664b594-0bb6-1ecb-def7-5673ba48c82b   ---
        "created_by",//a664b594-0bb6-1ecb-def7-5673ba48c82b   ---
        "description",//Тестовое описание
        "deleted",//0
        "assigned_user_id",//a664b594-0bb6-1ecb-def7-5673ba48c82b   ---
        "first_name",//Тестовый контракт 123450
        "last_name",//Тестоваяфамилия
        "title",//Тестовая должность
        "do_not_call",//0
        "phone_home",//null
        "phone_mobile",//null
        "phone_work",//+7-(903)-3216541
        "phone_other",//null
        "phone_fax",//null
        "primary_address_street",//Тестовая улица
        "primary_address_city",//Тестовый город
        "primary_address_state",//Тестовая область
        "primary_address_postalcode",//123450
        "primary_address_country",//Страна
        "latitude",//null
        "longitude",//null
        "primary_address_district",//Тестовый район
        "primary_address_building",//Тестовый дом
        "primary_address_note",//Тестовое примечание
        "converted",//0
        "lead_source",//Cold Call
        "status",//New
        "account_name",//Тестовое название контракта
        "contact_id",//null
        "account_id",//null
        "opportunity_id",//null
        "denial_reason"//null
      );

$text = "";
      $text2 = "";
      $leadId = self::create_guid();
      $time = time();
      $time0 = $time-date("Z",time());
      $timeStamp = date("Y-m-d H:i:s",$time0);
      $toReturn = array();
      $toExecute = array("id"=>$leadId,"date_entered"=>$timeStamp,"date_modified"=>$timeStamp);

if (!(isset($array["modified_user_id"]) and (($array["modified_user_id"]."") != "") and ((self::$userAr["modified_user_id"]."") != ""))) {
        $array["modified_user_id"] = self::$userAr["modified_user_id"];
      }
      if (!(isset($array["assigned_user_id"]) and (($array["assigned_user_id"]."") != "") and ((self::$userAr["assigned_user_id"]."") != ""))) {
        $array["assigned_user_id"] = self::$userAr["assigned_user_id"];
      }
      if ((self::$userAr["created_by"]."") != "") {
        if (!(isset($array["modified_user_id"]) and (($array["modified_user_id"]."") != ""))) {
          $array["modified_user_id"] = self::$userAr["created_by"];
        }
        if (!(isset($array["assigned_user_id"]) and (($array["assigned_user_id"]."") != ""))) {
          $array["assigned_user_id"] = self::$userAr["created_by"];
        }
        if (!(isset($array["created_by"]) and (($array["created_by"]."") != ""))) {
          $array["created_by"] = self::$userAr["created_by"];
        }
      }

foreach ($array as $field => $value) {
        if (in_array($field,$allowedFields)) {
          if (!$text) {$text = "(`id`, `date_entered`, `date_modified`, `$field`";$text2 = "(:id, :date_entered, :date_modified, :$field";} else {$text .= ", `$field`";$text2 .= ", :$field";}
          $toExecute[$field] = $value;
        }
      }

$toReturn = $toExecute;$toReturn["date"] = $timeStamp;$toReturn["unixTime"] = $time;$toReturn["unixTime0"] = $time0;unset($toReturn["date_entered"]);unset($toReturn["date_modified"]);

$email = false;
      if (isset($array["email"])) {
        $toReturn["email"] = $array["email"];
        $toReturn["emailCorrect"] = false;
        $toReturn["emailExist"] = false;
        if (self::isEmail($array["email"])) {
          $email = $array["email"];
          $toReturn["emailCorrect"] = true;
        }
      }

$text .= ")";$text2 .= ")";
      if (count($toExecute) > 3) {
        $stmt = self::PDO()->prepare("INSERT INTO `" . self::$leadTable . "` $text VALUES $text2;");
        if ($stmt->execute($toExecute)) {
          if ($email) {
            $emailId = self::getIdByEmailCreateIfNotExist($email);
            $toReturn["emailExist"] = self::$lastEmailExist;
            $toReturn["emailPlusLeadCreate"] = false;
            $toReturn["emailPlusLeadId"] = false;
            if ($emailId) {
              $toReturn["emailId"] = $emailId;
              
              $emailPlusLeadId = self::create_guid();
              $toExecute = array("id"=>$emailPlusLeadId,"email_address_id"=>$emailId,"bean_id"=>$leadId,"bean_module"=>"Leads","primary_address"=>1,"date_created"=>$timeStamp,"date_modified"=>$timeStamp);

$stmt = self::PDO()->prepare("INSERT INTO `" . self::$emailPlusLeadTable . "` (`id`, `email_address_id`, `bean_id`, `bean_module`, `primary_address`, `date_created`, `date_modified`) VALUES (:id, :email_address_id, :bean_id, :bean_module, :primary_address, :date_created, :date_modified);");//id - id для связи,email_address_id - id e-mail адреса,bean_id - Id предварительного контакта(таблицы Leads),bean_module = "Leads",primary_address = 1(означает что основной e-mail),date_created - дата создания,date_modified - дата модификации
              if ($stmt->execute($toExecute)) {
                $toReturn["emailPlusLeadCreate"] = true;
                $toReturn["emailPlusLeadId"] = $emailPlusLeadId;
              }
            }
          }
          return $toReturn;
        }
      }
    }
    return false;
  }

public static function isEmail($email,$flag = 0) {
    if (!preg_match("|^([a-z0-9_\.\-]{1,40})@([a-z0-9\.\-]{1,30})\.([a-z]{2,4})|is", strtolower($email))) {
      return false;
    }
    if ($flag) {
      list ($Username, $Domain) = split ("@",$email);
      if (getmxrr ($Domain, $MXHost)) {
        return true;
      } else {
        if (fsockopen ($Domain, 25, $errno, $errstr, 5)) {
          return true;
        } else {
          return false;
        }
      }
    }
    return true;
  }

public static function ensure_length(&$string,$length) {
    $strlen = strlen($string);
      if ($strlen < $length) {
        $string = str_pad($string,$length,"0");
      } else if($strlen > $length) {
        $string = substr($string,0,$length);
      }
    }

public static function create_guid_section($characters) {
    $return = "";
    for ($i=0;$i<$characters;$i++) {
      $return .= dechex(mt_rand(0,15));
    }
    return $return;
  }

public static function create_guid() {
    $microTime = microtime();
    list($a_dec,$a_sec) = explode(" ",$microTime);

$dec_hex = dechex($a_dec * 1000000);
    $sec_hex = dechex($a_sec);

self::ensure_length($dec_hex,5);
    self::ensure_length($sec_hex,6);

$guid = "";
    $guid .= $dec_hex;
    $guid .= self::create_guid_section(3);
    $guid .= '-';
    $guid .= self::create_guid_section(4);
    $guid .= '-';
    $guid .= self::create_guid_section(4);
    $guid .= '-';
    $guid .= self::create_guid_section(4);
    $guid .= '-';
    $guid .= $sec_hex;
    $guid .= self::create_guid_section(6);

return $guid;
  }

public static function PDO() {
    if (!self::$pdo) {
      self::$pdo = new PDO("mysql:host=localhost;dbname=".self::$db,self::$user,self::$password,array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
    }
    return self::$pdo;
  }

public static function checkEmailExist4($email=false) {
    if (($email !== false) and $email and (self::PDO() !== false) and self::isEmail($email)) {
      if (isset(self::$chackedEmailAr[$email])) {
        return self::$chackedEmailAr[$email];
      } else {
        $email = strtoupper($email);
        $stmt = self::PDO()->prepare("SELECT * FROM `" . self::$emailTable . "` WHERE `email_address_caps` = :email AND `deleted` = 0;");
        $result = $stmt->execute(array("email"=>$email));
        $row = $stmt->fetch();
        self::$chackedEmailAr[$email] = $row;
        return $row;
      }
    }
    return false;
  }

public static function emailPackAddToTmpTable($emails=false) {
    if (is_array($emails) and count($emails)) {
      $values = array();
      $text = "";
      $i = 0;
      foreach ($emails as $email) {
        $i++;
        if (!$text) {$text .= "(:email$i)";} else {$text .= ", (:email$i)";}
        $values["email$i"] = $email;
      }
      $stmt = self::PDO()->prepare("INSERT INTO `" . self::$emailTmpTable . "` (`email`) VALUES $text");
      return $stmt->execute($values);
    }
    return false;
  }

public static function checkEmailsExist($email=false) {
    if (($email !== false) and $email and (self::PDO() !== false)) {
      if (is_array($email)) {
        $resultAr = array();
        $createdTmpTable = false;
        $emailPackAddAr = array();//Массив содержащий пачку e-mail адресов которая будет добавлена в временную таблицу одним запросов
        $emailPackAddLen = 20;//Размер массива пакета e-mail адресов по достижению которого будет выполнен запрос на добавление e-mail адресоув в ременную таблицу
        foreach ($email  as $oneEmail) {
          if (self::isEmail($oneEmail)) {
            $oneEmail = strtoupper($oneEmail);
            if (isset(self::$chackedEmailAr[$oneEmail])) {
              $resultAr[$oneEmail] = self::$chackedEmailAr[$oneEmail];
            } else {
              self::$chackedEmailAr[$oneEmail] = false;
              if (!$createdTmpTable) {
                $stmt = self::PDO()->prepare("CREATE TEMPORARY TABLE `" . self::$emailTmpTable . "` (`email` varchar(150) NOT NULL) ENGINE=MyISAM  DEFAULT CHARSET=utf8");
                if ($result = $stmt->execute()) {$createdTmpTable = true;}
              }
              $emailPackAddAr[] = $oneEmail;
              if (count($emailPackAddAr) >= $emailPackAddLen) {
                self::emailPackAddToTmpTable($emailPackAddAr);
                $emailPackAddAr = array();
              }
            }
          }
        }
        if (count($emailPackAddAr)) {self::emailPackAddToTmpTable($emailPackAddAr);}
        unset($emailPackAddAr);
        $stmt = self::PDO()->prepare("SELECT * FROM `" . self::$emailTable . "`, `" . self::$emailTmpTable . "` WHERE `" . self::$emailTable . "`.`email_address_caps` = `" . self::$emailTmpTable . "`.`email`;");
        $result = $stmt->execute();
        $resultAr = array();
        while ($row = $stmt->fetch()) {
          if (isset($row["email_address_caps"]) and self::isEmail($row["email_address_caps"]) and isset($row["id"]) and $row["id"]) {//Вторая проверка может и не нужна
            $row["email_address_caps"] = strtoupper($row["email_address_caps"]);
            self::$chackedEmailAr[$row["email_address_caps"]] = $row;
            $resultAr[] = $row;
          }
        }
        return $resultAr;
          //К этому моменту массив заполнен

} elseif (self::isEmail($email)) {
        if (isset(self::$chackedEmailAr[$email])) {
          return self::$chackedEmailAr[$email];
        } else {
          $email = strtoupper($email);
          $stmt = self::PDO()->prepare("SELECT * FROM `" . self::$emailTable . "` WHERE `email_address_caps` = :email AND `deleted` = 0;");
          $result = $stmt->execute(array("email"=>$email));
          $row = $stmt->fetch();
          self::$chackedEmailAr[$email] = $row;
          return $row;
        }
      }
    }
    return false;
  }

public static function getIdByEmailCreateIfNotExist($email=false) {
    if (self::isEmail($email)) {
      $emailOriginalCase = $email;
      $email = strtoupper($email);
      if (isset(self::$chackedEmailAr[$email]) and isset(self::$chackedEmailAr[$email]["id"]) and self::$chackedEmailAr[$email]["id"]) {
        self::$lastEmailExist = "yes";
        return self::$chackedEmailAr[$email]["id"];
      } elseif (($tmp = self::checkEmailsExist($email)) and isset($tmp["id"]) and $tmp["id"]) {
        self::$lastEmailExist = "yes";
        return $tmp["id"];
      } else {
        $timeStamp = date("Y-m-d H:i:s",(time()-date("Z",time())));
        $id = self::create_guid();
        $toExecute = array("id"=>$id,"email_address"=>$emailOriginalCase,"email_address_caps"=>$email,"date_created"=>$timeStamp,"date_modified"=>$timeStamp);
        $stmt = self::PDO()->prepare("INSERT INTO `" . self::$emailTable . "` (`id`, `email_address`, `email_address_caps`, `date_created`, `date_modified`) VALUES (:id, :email_address, :email_address_caps, :date_created, :date_modified);");
        if ($stmt->execute($toExecute)) {
          self::$lastEmailExist = "create";
          return $id;
        }
      }
    }
    self::$lastEmailExist = "error";
    return false;
  }
}

На всякий случай тут поясню по файлам, с помощью которых можно загрузить предварительные контакты из CSV или Excel.

file.csv - сам файл который распарсивается и из которого осуществляется загрузка.

myLead.php - файл организует непосредстувенно загрузку предварительных контактов в таблицу базы данных "leads", а так же проверят существование e-mail адреса в базе, при необходимости создавая его, а так же устанавливая соответствие между таблицой leads и таблицей email_addresses.

load.php - осуществляет разбор полей CSV файла, а так же объединением некоторых полей в одно, кроме того организует maping загрузки сопоставляя какие поля файла должны попасть в какие поля таблицы. На первой итерации осуществляется сбор всех e-mail адресов в единый массив, после первого прохода этот массив переадётся классу myLead чтобы в минимальное количество запросов выяснить какие e-mail адреса уже присутствуют в базе данных и получить их id. Далее эта информация кешируется в мпециальный массив. На втором проходе выполняется запрос на создание предвариетельного контракта по одному за итерацию, класс автоматически определяет нужно ли создавать E-mail адреса и создаёт связи между ними.

Остальные файлы содержат лог в JSON-формате, а именно какие именно записии и под какими id были созданы за соответствующий проход, в том числе это касается созданных записей E-mail, а так же Id записи в связывающей таблице для таблиц leads и email_addresses.

При необходимости в верхней части файла myLeads можно задать Id пользователей от имени которых будет создана запись а так же Id ответственного за предварительный контракт пользователей. Эти же параметры можно передавать в массиве при генерации каждого lead. Если передавать значения id пользователей в массиве, то оно для данного лида переопределит значение указанное в настройках файла myLeads.

Комментарии