alert('阿里旺旺为必填项');history.back();";
exit;
}
// Website source validation
if (strpos($cs_code, '官网') !== false) {
$cs_from = 3;
}
if ($cs_from == 3 && empty($tel_1) && empty($whatsapp_1) && empty($wechat_1)) {
echo "";
exit;
}
// Market customer validation
if ($cs_from == 8 && empty($wechat_1)) {
echo "";
exit;
}
// Facebook validation
if ($cs_from == 12 && empty($facebook_1)) {
echo "";
exit;
}
}
// Determine action type
$act = empty($id) || !is_numeric($id) ? 'addSave' : 'editSave';
if ($act === 'editSave') {
// Verify customer ownership - 将bind_param改为SQL拼接
$id = intval($id); // 确保ID是整数，防止SQL注入
$sql = "SELECT cs_belong FROM customer WHERE id = " . $id;
$result = $conn->query($sql);
if ($row = $result->fetch_assoc()) {
if ($row['cs_belong'] != $cs_belong) {
echo "";
exit;
}
} else {
echo "";
exit;
}
}
// Validate customer code
if (empty($cs_code)) {
echo "";
exit;
}
$customer_id = is_numeric($id) ? intval($id) : 0;
// Check for duplicate customer information
$checkStr = "SELECT c.*, cc.*
FROM customer c
LEFT JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.id != " . $customer_id." AND ( 1=2 ";
$Dupli = "";
/* 暂时不需要这些检查 START */
// Company name similarity check
//if (!empty($cs_company)) {
// $companyWords = explode(' ', strtolower($cs_company));
// foreach ($companyWords as $word) {
// if (strlen($word) > 3) { // Only check words longer than 3 characters
// $checkStr .= " OR LOWER(c.cs_company) LIKE '%" . $conn->real_escape_string($word) . "%'";
// }
// }
//}
// Address similarity check
//if (!empty($cs_address)) {
// $addressWords = explode(' ', strtolower($cs_address));
// foreach ($addressWords as $word) {
// if (strlen($word) > 3) { // Only check words longer than 3 characters
// $checkStr .= " OR LOWER(c.cs_address) LIKE '%" . $conn->real_escape_string($word) . "%'";
// }
// }
//}
/* 暂时不需要这些检查 END */
// Check all contacts for duplicates
foreach ($contacts as $contact) {
//遍历处理空格
foreach ($contact as $key => $value) {
$contact[$key] = trim($value);
}
// Check all phone numbers
for ($i = 1; $i <= 3; $i++) {
$tel_field = 'tel_' . $i;
if (!empty($contact[$tel_field])) {
$tel_format = numFormat($contact[$tel_field]);
// Remove country code and spaces for better matching
$tel_clean = preg_replace('/[^0-9]/', '', $tel_format);
if(!empty($tel_clean))
{
$checkStr .= " OR cc.tel_1_format LIKE '%" . substr($tel_clean, -9) . "%'" .
" OR cc.tel_2_format LIKE '%" . substr($tel_clean, -9) . "%'" .
" OR cc.tel_3_format LIKE '%" . substr($tel_clean, -9) . "%'" .
" OR cc.wechat_1 LIKE '%" . substr($tel_clean, -9) . "%'" .
" OR cc.wechat_2 LIKE '%" . substr($tel_clean, -9) . "%'" .
" OR cc.wechat_3 LIKE '%" . substr($tel_clean, -9) . "%'";
$Dupli .= "电话" . $i . ":" . $contact[$tel_field] . " ";
}
}
}
// Check all email addresses
for ($i = 1; $i <= 3; $i++) {
$email_field = 'email_' . $i;
if (!empty($contact[$email_field])) {
$contact[$email_field]=trim($contact[$email_field]);
$email = strtolower($contact[$email_field]);
$checkStr .= " OR LOWER(cc.email_1) = '" . $conn->real_escape_string($email) . "'" .
" OR LOWER(cc.email_2) = '" . $conn->real_escape_string($email) . "'" .
" OR LOWER(cc.email_3) = '" . $conn->real_escape_string($email) . "'";
$Dupli .= "邮箱" . $i . ":" . $contact[$email_field] . " ";
}
}
// Check all WhatsApp numbers
for ($i = 1; $i <= 3; $i++) {
$whatsapp_field = 'whatsapp_' . $i;
if (!empty($contact[$whatsapp_field])) {
$whatsapp_format = numFormat($contact[$whatsapp_field]);
$whatsapp_clean = preg_replace('/[^0-9]/', '', $whatsapp_format);
if(!empty($whatsapp_clean))
{
$checkStr .= " OR cc.whatsapp_1_format LIKE '%" . substr($whatsapp_clean, -9) . "%'" .
" OR cc.whatsapp_2_format LIKE '%" . substr($whatsapp_clean, -9) . "%'" .
" OR cc.whatsapp_3_format LIKE '%" . substr($whatsapp_clean, -9) . "%'" .
" OR cc.tel_1_format LIKE '%" . substr($whatsapp_clean, -9) . "%'" .
" OR cc.tel_2_format LIKE '%" . substr($whatsapp_clean, -9) . "%'" .
" OR cc.tel_3_format LIKE '%" . substr($whatsapp_clean, -9) . "%'";
$Dupli .= "WhatsApp" . $i . ":" . $contact[$whatsapp_field] . " ";
}
}
}
// Check all WeChat accounts
for ($i = 1; $i <= 3; $i++) {
$wechat_field = 'wechat_' . $i;
if (!empty($contact[$wechat_field])) {
$wechat = $contact[$wechat_field];
$checkStr .= " OR cc.wechat_1 LIKE '%" . $conn->real_escape_string($wechat) . "%'" .
" OR cc.wechat_2 LIKE '%" . $conn->real_escape_string($wechat) . "%'" .
" OR cc.wechat_3 LIKE '%" . $conn->real_escape_string($wechat) . "%'" .
" OR cc.tel_1_format LIKE '%" . $conn->real_escape_string($wechat) . "%'" .
" OR cc.tel_2_format LIKE '%" . $conn->real_escape_string($wechat) . "%'" .
" OR cc.tel_3_format LIKE '%" . $conn->real_escape_string($wechat) . "%'";
$Dupli .= "微信" . $i . ":" . $contact[$wechat_field] . " ";
}
}
// Check all LinkedIn accounts
for ($i = 1; $i <= 3; $i++) {
$linkedin_field = 'linkedin_' . $i;
if (!empty($contact[$linkedin_field])) {
$linkedin = strtolower($contact[$linkedin_field]);
$checkStr .= " OR LOWER(cc.linkedin_1) LIKE '%" . $conn->real_escape_string($linkedin) . "%'" .
" OR LOWER(cc.linkedin_2) LIKE '%" . $conn->real_escape_string($linkedin) . "%'" .
" OR LOWER(cc.linkedin_3) LIKE '%" . $conn->real_escape_string($linkedin) . "%'";
$Dupli .= "LinkedIn" . $i . ":" . $contact[$linkedin_field] . " ";
}
}
// Check all Facebook accounts
for ($i = 1; $i <= 3; $i++) {
$facebook_field = 'facebook_' . $i;
if (!empty($contact[$facebook_field])) {
$facebook = strtolower($contact[$facebook_field]);
$checkStr .= " OR LOWER(cc.facebook_1) LIKE '%" . $conn->real_escape_string($facebook) . "%'" .
" OR LOWER(cc.facebook_2) LIKE '%" . $conn->real_escape_string($facebook) . "%'" .
" OR LOWER(cc.facebook_3) LIKE '%" . $conn->real_escape_string($facebook) . "%'";
$Dupli .= "Facebook" . $i . ":" . $contact[$facebook_field] . " ";
}
}
// Check all Alibaba accounts
for ($i = 1; $i <= 3; $i++) {
$alibaba_field = 'alibaba_' . $i;
if (!empty($contact[$alibaba_field])) {
$alibaba = strtolower($contact[$alibaba_field]);
if (strlen($alibaba) < 10) {
$checkStr .= " OR LOWER(cc.alibaba_1) LIKE '" . $conn->real_escape_string($alibaba) . "'" .
" OR LOWER(cc.alibaba_2) LIKE '" . $conn->real_escape_string($alibaba) . "'" .
" OR LOWER(cc.alibaba_3) LIKE '" . $conn->real_escape_string($alibaba) . "'";
} else {
$alibaba_clean = preg_replace('/[^0-9a-zA-Z]/', '', $alibaba);
$checkStr .= " OR LOWER(cc.alibaba_1) LIKE '%" . substr($alibaba_clean, -9) . "%'" .
" OR LOWER(cc.alibaba_2) LIKE '%" . substr($alibaba_clean, -9) . "%'" .
" OR LOWER(cc.alibaba_3) LIKE '%" . substr($alibaba_clean, -9) . "%'";
}
$Dupli .= "阿里旺旺" . $i . ":" . $contact[$alibaba_field] . " ";
}
}
}
$checkStr .= " ) ORDER BY c.id ASC";
if ($allowedit != 1) {
$result = $conn->query($checkStr);
if ($result && $result->num_rows > 0) {
$row = $result->fetch_assoc();
// Get owner name
$ownerResult = $conn->query("SELECT em_user FROM employee WHERE id = " . $row['cs_belong']);
$ownerRow = $ownerResult->fetch_assoc();
$owner = textUncode($ownerRow['em_user']);
// Determine who entered first
if (strtotime($cs_addtime) > strtotime($row['cs_addtime'])) {
$tstr = "INSERT INTO logrecord (loginName, loginIp, loginTime, loginAct) VALUES ('" .
$_SESSION['employee_name'] . "', '" . getIp() . "', '" . date('Y-m-d H:i:s') . "', '" .
$_SESSION['employee_name'] . "编辑客户\"" . $cs_code . "\"，该客户与\"" .
textUncode($row['cs_code']) . "\"高度类似，
重复项为:" . $Dupli . "
客户由:" .
$_SESSION['employee_name'] . $cs_addtime . "首次录入')";
} else {
$tstr = "INSERT INTO logrecord (loginName, loginIp, loginTime, loginAct) VALUES ('" .
$_SESSION['employee_name'] . "', '" . getIp() . "', '" . date('Y-m-d H:i:s') . "', '" .
$_SESSION['employee_name'] . "编辑客户\"" . $cs_code . "\"，该客户与\"" .
textUncode($row['cs_code']) . "\"高度类似，
重复项为:" . $Dupli . "
客户由:" .
$owner . $row['cs_addtime'] . "首次录入')";
}
$conn->query($tstr);
echo "";
exit;
}
else
{
//全文检索再检查一次
//先去掉联系方式中的特殊字符
//用联系方式进行全文fullindex检索，用相关性分数大于百分70 的进行检查
// 根据不同联系方式类型进行专门检查
$duplicateFound = false;
$matchDetails = '';
$matchScore = 0;
$matchCustomerId = 0;
$matchCustomerCode = '';
$matchOwner = '';
$matchAddTime = '';
// 编辑模式下需要排除自己的ID
$excludeId = ($act === 'editSave' && is_numeric($id)) ? intval($id) : 0;
// 1. 优先检查邮箱 - 邮箱是最精确的匹配
$emailTerms = [];
foreach ($contacts as $contact) {
for ($i = 1; $i <= 3; $i++) {
$field = 'email_' . $i;
if (!empty($contact[$field])) {
$email = strtolower(trim($contact[$field]));
if (strpos($email, '@') !== false) {
$emailTerms[] = $email;
}
}
}
}
if (!empty($emailTerms)) {
$emailQuery = "SELECT c.id, c.cs_code, c.cs_belong, c.cs_addtime, cc.email_1, cc.email_2, cc.email_3
FROM customer c
JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.cs_belong != " . $_SESSION['employee_id'] . "
AND c.id != " . $excludeId . " AND (";
$emailConditions = [];
foreach ($emailTerms as $email) {
$emailConditions[] = "cc.email_1 = '" . $conn->real_escape_string($email) . "'";
$emailConditions[] = "cc.email_2 = '" . $conn->real_escape_string($email) . "'";
$emailConditions[] = "cc.email_3 = '" . $conn->real_escape_string($email) . "'";
}
$emailQuery .= implode(" OR ", $emailConditions) . ") LIMIT 1";
$emailResult = $conn->query($emailQuery);
if ($emailResult && $emailResult->num_rows > 0) {
$row = $emailResult->fetch_assoc();
$matchCustomerId = $row['id'];
$matchCustomerCode = $row['cs_code'];
$matchOwner = $row['cs_belong'];
$matchAddTime = $row['cs_addtime'];
$matchDetails = "邮箱完全匹配";
$matchScore = 0.95; // 邮箱精确匹配，高可信度
$duplicateFound = true;
}
}
// 2. 检查电话号码与WhatsApp - 清理后进行后缀匹配
if (!$duplicateFound) {
$phoneTerms = [];
foreach ($contacts as $contact) {
// 收集所有电话号码
for ($i = 1; $i <= 3; $i++) {
$fields = ['tel_' . $i, 'whatsapp_' . $i];
foreach ($fields as $field) {
if (!empty($contact[$field])) {
$cleaned = preg_replace('/[^0-9]/', '', $contact[$field]);
if (strlen($cleaned) > 7) { // 至少8位有效数字
$phoneTerms[] = $cleaned;
}
}
}
}
}
if (!empty($phoneTerms)) {
$phoneQuery = "SELECT c.id, c.cs_code, c.cs_belong, c.cs_addtime,
cc.tel_1_format, cc.tel_2_format, cc.tel_3_format,
cc.whatsapp_1_format, cc.whatsapp_2_format, cc.whatsapp_3_format
FROM customer c
JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.cs_belong != " . $_SESSION['employee_id'] . "
AND c.id != " . $excludeId . " AND (";
$phoneConditions = [];
foreach ($phoneTerms as $phone) {
// 使用后8位进行匹配，避免国家代码等差异
$suffix = substr($phone, -8);
if (strlen($suffix) == 8) {
$phoneConditions[] = "cc.tel_1_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
$phoneConditions[] = "cc.tel_2_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
$phoneConditions[] = "cc.tel_3_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
$phoneConditions[] = "cc.whatsapp_1_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
$phoneConditions[] = "cc.whatsapp_2_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
$phoneConditions[] = "cc.whatsapp_3_format LIKE '%" . $conn->real_escape_string($suffix) . "'";
}
}
if (!empty($phoneConditions)) {
$phoneQuery .= implode(" OR ", $phoneConditions) . ") LIMIT 1";
$phoneResult = $conn->query($phoneQuery);
if ($phoneResult && $phoneResult->num_rows > 0) {
$row = $phoneResult->fetch_assoc();
$matchCustomerId = $row['id'];
$matchCustomerCode = $row['cs_code'];
$matchOwner = $row['cs_belong'];
$matchAddTime = $row['cs_addtime'];
$matchDetails = "电话号码后8位匹配";
$matchScore = 0.90; // 电话匹配，高可信度
$duplicateFound = true;
}
}
}
}
// 3. 检查社交媒体账号 (alibaba/wechat/facebook/linkedin)
if (!$duplicateFound) {
$socialTerms = [];
$socialFields = [
'alibaba' => '阿里旺旺',
'wechat' => '微信',
'facebook' => 'Facebook',
'linkedin' => 'LinkedIn'
];
foreach ($contacts as $contact) {
foreach ($socialFields as $field => $label) {
for ($i = 1; $i <= 3; $i++) {
$fieldName = $field . '_' . $i;
if (!empty($contact[$fieldName])) {
$value = trim($contact[$fieldName]);
if (strlen($value) > 3) { // 至少4个字符
$socialTerms[] = [
'type' => $field,
'label' => $label,
'value' => $value
];
}
}
}
}
}
if (!empty($socialTerms)) {
foreach ($socialTerms as $term) {
$field = $term['type'];
$value = $term['value'];
$label = $term['label'];
// 根据社交媒体类型构建不同的查询
$socialQuery = "SELECT c.id, c.cs_code, c.cs_belong, c.cs_addtime FROM customer c
JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.cs_belong != " . $_SESSION['employee_id'] . "
AND c.id != " . $excludeId . " AND (";
// 根据社交账号类型确定匹配方式
if ($field == 'alibaba' || $field == 'wechat') {
// 阿里旺旺和微信用精确匹配
$socialQuery .=
"cc.{$field}_1 = '" . $conn->real_escape_string($value) . "' OR " .
"cc.{$field}_2 = '" . $conn->real_escape_string($value) . "' OR " .
"cc.{$field}_3 = '" . $conn->real_escape_string($value) . "'";
} else {
// Facebook和LinkedIn用模糊匹配
$socialQuery .=
"cc.{$field}_1 LIKE '%" . $conn->real_escape_string($value) . "%' OR " .
"cc.{$field}_2 LIKE '%" . $conn->real_escape_string($value) . "%' OR " .
"cc.{$field}_3 LIKE '%" . $conn->real_escape_string($value) . "%'";
}
$socialQuery .= ") LIMIT 1";
$socialResult = $conn->query($socialQuery);
if ($socialResult && $socialResult->num_rows > 0) {
$row = $socialResult->fetch_assoc();
$matchCustomerId = $row['id'];
$matchCustomerCode = $row['cs_code'];
$matchOwner = $row['cs_belong'];
$matchAddTime = $row['cs_addtime'];
$matchDetails = $label . "账号匹配";
// 不同社交媒体账号的可信度
switch ($field) {
case 'alibaba':
$matchScore = 0.85;
break;
case 'wechat':
$matchScore = 0.85;
break;
case 'facebook':
$matchScore = 0.80;
break;
case 'linkedin':
$matchScore = 0.80;
break;
default:
$matchScore = 0.75;
}
$duplicateFound = true;
break; // 找到匹配就退出循环
}
}
}
}
// 4. 最后尝试全文检索 - 作为补充检测手段
if (!$duplicateFound) {
// 准备全文检索字符串
$searchTerms = [];
// 处理所有联系人信息用于检索
foreach ($contacts as $contact) {
// 添加联系人名称
if (!empty($contact['contact_name'])) {
$searchTerms[] = textUncode($contact['contact_name']);
}
// 所有联系方式的组合检索
$contactFields = [
'tel', 'email', 'whatsapp', 'wechat',
'linkedin', 'facebook', 'alibaba'
];
foreach ($contactFields as $fieldType) {
for ($i = 1; $i <= 3; $i++) {
$field = $fieldType . '_' . $i;
if (!empty($contact[$field])) {
// 针对不同类型的联系方式进行不同清理
if ($fieldType == 'tel' || $fieldType == 'whatsapp') {
$cleaned = preg_replace('/[^0-9]/', '', $contact[$field]);
if (strlen($cleaned) > 5) {
$searchTerms[] = $cleaned;
}
} else if ($fieldType == 'email') {
$cleaned = strtolower(trim($contact[$field]));
if (strpos($cleaned, '@') !== false) {
$searchTerms[] = $cleaned;
}
} else {
$searchTerms[] = trim($contact[$field]);
}
}
}
}
}
// 如果有搜索条件
if (!empty($searchTerms)) {
// 创建MATCH AGAINST语句的词条
$searchStr = implode(' ', array_unique(array_filter($searchTerms)));
// 确保搜索字符串不为空
if (!empty($searchStr)) {
// 构建全文检索SQL
$ftQuery = "SELECT c.id, c.cs_code, c.cs_belong, c.cs_addtime,
MATCH( cc.tel_1, cc.tel_2, cc.tel_3,
cc.email_1, cc.email_2, cc.email_3,
cc.whatsapp_1, cc.whatsapp_2, cc.whatsapp_3,
cc.wechat_1, cc.wechat_2, cc.wechat_3
)
AGAINST('" . $conn->real_escape_string($searchStr) . "' IN NATURAL LANGUAGE MODE) AS score
FROM customer c
JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.id != " . $excludeId . "
HAVING score > 0.7
ORDER BY score DESC
LIMIT 1";
$ftResult = $conn->query($ftQuery);
if(!($ftResult && $ftResult->num_rows > 0 ))
{
$ftQuery = "SELECT c.id, c.cs_code, c.cs_belong, c.cs_addtime,
MATCH( cc.alibaba_1, cc.alibaba_2, cc.alibaba_3,
cc.facebook_1, cc.facebook_2, cc.facebook_3,
cc.linkedin_1, cc.linkedin_2, cc.linkedin_3)
AGAINST('" . $conn->real_escape_string($searchStr) . "' IN NATURAL LANGUAGE MODE) AS score
FROM customer c
JOIN customer_contact cc ON c.id = cc.customer_id
WHERE c.id != " . $excludeId . "
HAVING score > 0.7
ORDER BY score DESC
LIMIT 1";
$ftResult = $conn->query($ftQuery);
}
if ($ftResult && $ftResult->num_rows > 0) {
$row = $ftResult->fetch_assoc();
$matchCustomerId = $row['id'];
$matchCustomerCode = $row['cs_code'];
$matchOwner = $row['cs_belong'];
$matchAddTime = $row['cs_addtime'];
$matchDetails = "全文检索相似度" . number_format($row['score'] * 100, 1) . "%";
$matchScore = $row['score'];
$duplicateFound = true;
}
}
}
}
// 如果找到重复客户，记录并提示
if ($duplicateFound) {
// 获取客户所有者姓名
$ownerResult = $conn->query("SELECT em_user FROM employee WHERE id = " . $matchOwner);
$ownerRow = $ownerResult->fetch_assoc();
$owner = textUncode($ownerRow['em_user']);
// 确定谁先录入
if (strtotime($cs_addtime) > strtotime($matchAddTime)) {
$tstr = "INSERT INTO logrecord (loginName, loginIp, loginTime, loginAct) VALUES ('" .
$_SESSION['employee_name'] . "', '" . getIp() . "', '" . date('Y-m-d H:i:s') . "', '" .
$_SESSION['employee_name'] . "编辑客户\"" . $cs_code . "\"，该客户与\"" .
textUncode($matchCustomerCode) . "\"存在重复，
匹配类型:" . $matchDetails .
"
客户由:" . $owner . $matchAddTime . "首次录入')";
} else {
$tstr = "INSERT INTO logrecord (loginName, loginIp, loginTime, loginAct) VALUES ('" .
$_SESSION['employee_name'] . "', '" . getIp() . "', '" . date('Y-m-d H:i:s') . "', '" .
$_SESSION['employee_name'] . "编辑客户\"" . $cs_code . "\"，该客户与\"" .
textUncode($matchCustomerCode) . "\"存在重复，
匹配类型:" . $matchDetails .
"
客户由:" . $_SESSION['employee_name'] . $cs_addtime . "首次录入')";
}
$conn->query($tstr);
echo "";
exit;
}
}
}
// Save or update customer data
if ($act == "editSave" || $allowedit == 1) {
$hrefstr = "/customers.php?Keys=" . $keys . "&fliterBusiness=" . $fliterBusiness .
"&fliterDeal=" . $fliterDeal . "&Page=" . $page;
// 更新客户基本信息
$updateSql = "UPDATE customer SET
cs_code='" . $conn->real_escape_string($cs_code) . "',
cs_company='" . $conn->real_escape_string($cs_company) . "',
cs_country=" . $cs_country . ",
cs_from=" . $cs_from . ",
cs_address='" . $conn->real_escape_string($cs_address) . "',
cs_updatetime='" . $cs_updatetime . "',
cs_belong=" . $cs_belong . ",
cs_belongclient=" . $cs_belongClient . ",
cs_state=" . $cs_state . ",
cs_deal=" . $cs_deal . ",
cs_note='" . $conn->real_escape_string($cs_note) . "'";
// 处理cs_dealdate
if ($cs_deal == 3) {
$updateSql .= ", cs_dealdate = CASE WHEN cs_dealdate IS NULL THEN NOW() ELSE cs_dealdate END";
}
$updateSql .= " WHERE id=" . intval($id);
$conn->query($updateSql);
// 处理业务类型 - 先删除已有的业务类型
$conn->query("DELETE FROM customer_business_type WHERE customer_id = " . intval($id));
// 添加新的业务类型
if (!empty($cs_type) && is_array($cs_type)) {
foreach ($cs_type as $type_id) {
$type_id = intval($type_id);
if ($type_id > 0) {
$conn->query("INSERT INTO customer_business_type (customer_id, business_type_id)
VALUES (" . intval($id) . ", " . $type_id . ")");
}
}
}
// 处理联系人信息 - 首先删除已有的不在提交列表中的联系人
$existingContactIds = [];
foreach ($contacts as $contact) {
if (!empty($contact['id'])) {
$existingContactIds[] = (int)$contact['id'];
}
}
if (!empty($existingContactIds)) {
$idsToKeep = implode(',', $existingContactIds);
$deleteContactsSql = "DELETE FROM customer_contact WHERE customer_id = " . intval($id) .
" AND id NOT IN (" . $idsToKeep . ")";
} else {
$deleteContactsSql = "DELETE FROM customer_contact WHERE customer_id = " . intval($id);
}
$conn->query($deleteContactsSql);
// 处理联系人信息 - 更新或添加联系人
foreach ($contacts as $contact) {
$contact_id = !empty($contact['id']) ? (int)$contact['id'] : 0;
$contact_name = textEncode($contact['contact_name'] ?? '');
// 准备SQL字段和值
$fields = ['contact_name'];
$values = ["'" . $conn->real_escape_string($contact_name) . "'"];
$updates = ["contact_name = '" . $conn->real_escape_string($contact_name) . "'"];
// 处理所有联系方式类型
$methodTypes = ['tel', 'email', 'whatsapp', 'wechat', 'linkedin', 'facebook', 'alibaba'];
foreach ($methodTypes as $type) {
for ($i = 1; $i <= 3; $i++) {
$field = $type . '_' . $i;
$format_field = $field . '_format';
$bu_field = $field . '_bu';
$value = textEncode($contact[$field] ?? '');
$format_value = ($type == 'tel' || $type == 'whatsapp') ? numFormat($value) : '';
$bu_value = textEncode($contact[$bu_field] ?? $value);
// 添加字段名
$fields[] = $field;
$fields[] = $bu_field;
if ($type == 'tel' || $type == 'whatsapp') {
$fields[] = $format_field;
}
// 添加值
$values[] = "'" . $conn->real_escape_string($value) . "'";
$values[] = "'" . $conn->real_escape_string($bu_value) . "'";
if ($type == 'tel' || $type == 'whatsapp') {
$values[] = "'" . $conn->real_escape_string($format_value) . "'";
}
// 添加更新语句
$updates[] = $field . " = '" . $conn->real_escape_string($value) . "'";
$updates[] = $bu_field . " = '" . $conn->real_escape_string($bu_value) . "'";
if ($type == 'tel' || $type == 'whatsapp') {
$updates[] = $format_field . " = '" . $conn->real_escape_string($format_value) . "'";
}
}
}
if ($contact_id > 0) {
// 更新已有联系人
$updateContactSql = "UPDATE customer_contact SET " .
implode(", ", $updates) . ", updated_at = NOW() " .
"WHERE id = " . $contact_id . " AND customer_id = " . intval($id);
$conn->query($updateContactSql);
} else {
// 添加新联系人
$insertContactSql = "INSERT INTO customer_contact (" .
implode(", ", $fields) . ", customer_id, created_at, updated_at) VALUES (" .
implode(", ", $values) . ", " . intval($id) . ", NOW(), NOW())";
$conn->query($insertContactSql);
}
}
// Update tags
$conn->query("DELETE FROM tagtable WHERE customerId = " . intval($id));
foreach ($mytag as $tag) {
if (!empty(trim($tag))) {
$tagSql = "INSERT INTO tagtable (tagName, employeeId, customerId) VALUES ('" .
$conn->real_escape_string($tag) . "', " .
intval($_SESSION['employee_id']) . ", " .
intval($id) . ")";
$conn->query($tagSql);
}
}
echo "";
} else {
// Insert new customer record
$insertSql = "INSERT INTO customer (
cs_code, cs_company, cs_country, cs_from, cs_address,
cs_addtime, cs_updatetime, cs_belong, cs_belongClient,
cs_state, cs_deal, cs_note, cs_chain, is_silent, cs_dealdate
) VALUES (
'" . $conn->real_escape_string($cs_code) . "',
'" . $conn->real_escape_string($cs_company) . "',
" . $cs_country . ",
" . $cs_from . ",
'" . $conn->real_escape_string($cs_address) . "',
NOW(),
NOW(),
" . $cs_belong . ",
" . $cs_belongClient . ",
" . $cs_state . ",
" . $cs_deal . ",
'" . $conn->real_escape_string($cs_note) . "',
" . $cs_belong . ",
0,
" . ($cs_deal == 3 ? "NOW()" : "NULL") . "
)";
$conn->query($insertSql);
$new_customer_id = $conn->insert_id;
// Insert business types for new customer
if ($new_customer_id > 0 && !empty($cs_type) && is_array($cs_type)) {
foreach ($cs_type as $type_id) {
$type_id = intval($type_id);
if ($type_id > 0) {
$conn->query("INSERT INTO customer_business_type (customer_id, business_type_id)
VALUES (" . $new_customer_id . ", " . $type_id . ")");
}
}
}
// Insert contact information for all contacts
if ($new_customer_id > 0) {
foreach ($contacts as $contact) {
$contact_name = textEncode($contact['contact_name'] ?? '');
// 准备SQL字段和值
$fields = ['contact_name'];
$values = ["'" . $conn->real_escape_string($contact_name) . "'"];
// 处理所有联系方式类型
$methodTypes = ['tel', 'email', 'whatsapp', 'wechat', 'linkedin', 'facebook', 'alibaba'];
foreach ($methodTypes as $type) {
for ($i = 1; $i <= 3; $i++) {
$field = $type . '_' . $i;
$format_field = $field . '_format';
$bu_field = $field . '_bu';
$value = textEncode($contact[$field] ?? '');
$format_value = ($type == 'tel' || $type == 'whatsapp') ? numFormat($value) : '';
$bu_value = textEncode($contact[$bu_field] ?? $value);
// 添加字段名
$fields[] = $field;
$fields[] = $bu_field;
if ($type == 'tel' || $type == 'whatsapp') {
$fields[] = $format_field;
}
// 添加值
$values[] = "'" . $conn->real_escape_string($value) . "'";
$values[] = "'" . $conn->real_escape_string($bu_value) . "'";
if ($type == 'tel' || $type == 'whatsapp') {
$values[] = "'" . $conn->real_escape_string($format_value) . "'";
}
}
}
// 添加新联系人
$insertContactSql = "INSERT INTO customer_contact (" .
implode(", ", $fields) . ", customer_id, created_at, updated_at) VALUES (" .
implode(", ", $values) . ", " . $new_customer_id . ", NOW(), NOW())";
$conn->query($insertContactSql);
}
// Save tags for new customer
foreach ($mytag as $tag) {
if (!empty(trim($tag))) {
$tagSql = "INSERT INTO tagtable (tagName, employeeId, customerId) VALUES ('" .
$conn->real_escape_string($tag) . "', " .
intval($_SESSION['employee_id']) . ", " .
intval($new_customer_id) . ")";
$conn->query($tagSql);
}
}
}
echo "";
}
?>