igamebox
/
mtb_crm_asp


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906
							<?php
require_once 'conn.php';
checkLogin();
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>管理区域</title>
    <link rel="stylesheet" href="system/css/common.css" type="text/css" />
    <script src="system/js/jquery-1.7.2.min.js"></script>
    <script src="js/js.js"></script>
</head>
<body class="clear">
<?php
// Get query parameters
$page = $_GET['Page'] ?? '';
$keys = urlencode($_GET['Keys'] ?? '');
$fliterBusiness = $_GET['fliterBusiness'] ?? '';
$fliterDeal = $_GET['Deal'] ?? '';

// Get form data
$id = $_POST['id'] ?? '';
$cs_code = textEncode($_POST['cs_code'] ?? '');
$cs_company = textEncode($_POST['cs_company'] ?? '');
$cs_country = $_POST['cs_country'] ?? '';
$cs_from = $_POST['cs_from'] ?? '';
$cs_address = textEncode($_POST['cs_address'] ?? '');
$cs_type = $_POST['cs_type'] ?? []; // Changed to array for multi-select
$cs_belongclient = $_POST['cs_belongclient'] ?? '';
$cs_addtime = $_POST['cs_addtime'] ?? '';
$cs_updatetime = date('Y-m-d H:i:s');
$cs_belong = $_SESSION['employee_id'];
$cs_state = 1;
$cs_deal = $_POST['cs_deal'] ?? '';
$allowedit = $_POST['Permissions'] ?? '0';

// Get contact information from the form
$contacts = $_POST['contact'] ?? [];

// Validate numeric values
$allowedit = is_numeric($allowedit) ? $allowedit : 0;
$cs_country = (is_numeric($cs_country) && $cs_country !== '') ? $cs_country : 0;
$cs_from = (is_numeric($cs_from) && $cs_from !== '') ? $cs_from : 0;
$cs_deal = (is_numeric($cs_deal) && $cs_deal !== '') ? $cs_deal : 1;
$cs_belongClient = (is_numeric($cs_belongclient) && $cs_belongclient !== '') ? $cs_belongclient : 0;

$cs_note = htmlEncode($_POST['cs_note'] ?? '');
$mytag = textEncode($_POST['mytag'] ?? '');
$mytag = str_replace(['&#60;&#47;span&#62;&#60;span&#62;', '&#60;&#47;span&#62;', '&#60;span&#62;'], [',', '', ''], $mytag);
$mytag = explode(',', $mytag);


// Auto-detect source from code
if (strpos($cs_code, ';1688') !== false) {
    $cs_from = 1; // 1688
}
if (strpos($cs_code, ';阿里') !== false) {
    $cs_from = 2; // International station
}


// Get the first contact for validation (if any)
$primary_contact = !empty($contacts) ? current($contacts) : [];

// Check contact-specific validation requirements based on source
if ($allowedit != 1) {
    // Get the first contact's information for validation
    $contact_name = textEncode($primary_contact['contact_name'] ?? '');
    $tel_1 = textEncode($primary_contact['tel_1'] ?? '');
    $wechat_1 = textEncode($primary_contact['wechat_1'] ?? '');
    $whatsapp_1 = textEncode($primary_contact['whatsapp_1'] ?? '');
    $email_1 = textEncode($primary_contact['email_1'] ?? '');
    $alibaba_1 = textEncode($primary_contact['alibaba_1'] ?? '');
    $facebook_1 = textEncode($primary_contact['facebook_1'] ?? '');
    
    // Alibaba validation
    if (($cs_from == 1 || $cs_from == 2) && empty($alibaba_1)) {
        echo "<script>alert('阿里旺旺为必填项');history.back();</script>";
        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 "<script>alert('电话和WhatsApp为必填项');history.back();</script>";
        exit;
    }

    // Market customer validation
    if ($cs_from == 8 && empty($wechat_1)) {
        echo "<script>alert('微信为必填项');history.back();</script>";
        exit;
    }

    // Facebook validation
    if ($cs_from == 12 && empty($facebook_1)) {
        echo "<script>alert('Facebook为必填项');history.back();</script>";
        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 "<script>alert('抱歉，该客户属于另一业务,你没有权限修改');history.back();</script>";
            exit;
        }
    } else {
        echo "<script>alert('没有此客户!');history.back();</script>";
        exit;
    }
}

// Validate customer code
if (empty($cs_code)) {
    echo "<script>alert('客户编码不能为空');history.back();</script>";
    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']) . "\"高度类似，<br>重复项为:" . $Dupli . "<br>客户由:" .
                   $_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']) . "\"高度类似，<br>重复项为:" . $Dupli . "<br>客户由:" .
                   $owner . $row['cs_addtime'] . "首次录入')";
        }
        
        $conn->query($tstr);
        echo "<script>alert('warning.1.录入信息\\n与" . $owner . "客户编号:" . textUncode($row['cs_code']) . 
             "\\n高度类似，未能保存，请联系管理员核实！');history.back();</script>";
        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) . "\"存在重复，<br>匹配类型:" . $matchDetails . 
                       "<br>客户由:" . $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) . "\"存在重复，<br>匹配类型:" . $matchDetails . 
                       "<br>客户由:" . $_SESSION['employee_name'] . $cs_addtime . "首次录入')";
            }
            
            $conn->query($tstr);
            echo "<script>alert('warning.2.录入信息\\n与" . $owner . "客户编号:" . textUncode($matchCustomerCode) . 
                 "\\存在重复（" . $matchDetails . "），未能保存\\n请联系管理员核实！');history.back();</script>";
            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 "<script>location.href='$hrefstr';</script>";
} 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 "<script>location.href='customerAdd.php';</script>";
}
?>
</body>
</html>