qianqiuwanzi
113 lines
4.2 KiB
PHP
113 lines
4.2 KiB
PHP
<?php
|
||
/**
|
||
* EDM 筛选接口 GET/POST /api/marketing/edm.php
|
||
* action:
|
||
* industries 返回行业下拉数据
|
||
* regions 返回区域二级联动数据（省/州 -> 市/区，来自 persons.work_location / hometown）
|
||
* filter 按 行业 + 区域 + 关键词 筛选邮箱列表（persons.email + companies 关联联系人邮箱）
|
||
* 入参（filter）：industry / province / city / keyword / page / limit
|
||
*/
|
||
require_once __DIR__ . '/../common/Api.php';
|
||
$pdo = Api::boot(['module' => 'marketing']);
|
||
|
||
|
||
$action = $_REQUEST['action'] ?? 'filter';
|
||
$pdo = DB::getInstance()->getPdo();
|
||
|
||
// ---------- 行业下拉 ----------
|
||
if ($action === 'industries') {
|
||
$rows = $pdo->query(
|
||
"SELECT DISTINCT industry FROM companies WHERE industry IS NOT NULL AND industry <> '' AND is_active = 1 ORDER BY industry"
|
||
)->fetchAll(PDO::FETCH_COLUMN);
|
||
Response::success(['industries' => $rows]);
|
||
}
|
||
|
||
// ---------- 区域二级联动 ----------
|
||
if ($action === 'regions') {
|
||
// 从 persons.work_location 与 hometown 提取 省/州 与 市/区
|
||
$rows = $pdo->query(
|
||
"SELECT work_location AS loc FROM persons WHERE work_location IS NOT NULL AND work_location <> '' AND is_active = 1
|
||
UNION SELECT hometown AS loc FROM persons WHERE hometown IS NOT NULL AND hometown <> '' AND is_active = 1"
|
||
)->fetchAll(PDO::FETCH_COLUMN);
|
||
|
||
$regions = [];
|
||
foreach ($rows as $loc) {
|
||
$parts = preg_split('/[\s\-—–\/,，]+/u', trim($loc));
|
||
$province = $parts[0] ?? $loc;
|
||
$city = isset($parts[1]) && $parts[1] !== '' ? $parts[1] : '';
|
||
if ($province === '') continue;
|
||
if (!isset($regions[$province])) {
|
||
$regions[$province] = [];
|
||
}
|
||
if ($city !== '' && !in_array($city, $regions[$province], true)) {
|
||
$regions[$province][] = $city;
|
||
}
|
||
}
|
||
Response::success(['regions' => $regions]);
|
||
}
|
||
|
||
// ---------- 筛选邮箱列表 ----------
|
||
if ($action === 'filter') {
|
||
$industry = trim($_REQUEST['industry'] ?? '');
|
||
$province = trim($_REQUEST['province'] ?? '');
|
||
$city = trim($_REQUEST['city'] ?? '');
|
||
$keyword = trim($_REQUEST['keyword'] ?? '');
|
||
|
||
// 邮箱来源：persons 的 social_accounts(platform=email)，关联工作经历(company) 做行业过滤
|
||
$where = ["sa.platform = 'email'", "sa.is_active = 1", 'p.is_active = 1'];
|
||
$params = [];
|
||
|
||
if ($industry !== '') {
|
||
$where[] = "EXISTS (
|
||
SELECT 1 FROM person_work_experiences pwe
|
||
LEFT JOIN companies c ON c.id = pwe.company_id
|
||
WHERE pwe.person_id = p.id AND pwe.is_active = 1 AND c.industry = ?
|
||
)";
|
||
$params[] = $industry;
|
||
}
|
||
if ($province !== '') {
|
||
$where[] = "(p.work_location LIKE ? OR p.hometown LIKE ?)";
|
||
$params[] = "%$province%";
|
||
$params[] = "%$province%";
|
||
}
|
||
if ($city !== '') {
|
||
$where[] = "(p.work_location LIKE ? OR p.hometown LIKE ?)";
|
||
$params[] = "%$city%";
|
||
$params[] = "%$city%";
|
||
}
|
||
if ($keyword !== '') {
|
||
$where[] = '(p.full_name LIKE ? OR sa.account_id LIKE ?)';
|
||
$like = "%$keyword%";
|
||
array_push($params, $like, $like);
|
||
}
|
||
$whereSql = implode(' AND ', $where);
|
||
|
||
$stmt = $pdo->prepare(
|
||
"SELECT COUNT(*) FROM social_accounts sa
|
||
LEFT JOIN persons p ON p.id = sa.owner_id
|
||
WHERE $whereSql"
|
||
);
|
||
$stmt->execute($params);
|
||
$total = (int)$stmt->fetchColumn();
|
||
|
||
[$page, $limit] = pageParams(20);
|
||
$offset = ($page - 1) * $limit;
|
||
$stmt = $pdo->prepare(
|
||
"SELECT sa.id, sa.account_id AS email, p.id AS person_id, p.full_name, p.work_location, p.hometown,
|
||
(SELECT c.display_name FROM person_work_experiences pwe
|
||
LEFT JOIN companies c ON c.id = pwe.company_id
|
||
WHERE pwe.person_id = p.id AND pwe.is_current = 1 LIMIT 1) AS company_name
|
||
FROM social_accounts sa
|
||
LEFT JOIN persons p ON p.id = sa.owner_id
|
||
WHERE $whereSql
|
||
ORDER BY sa.id DESC
|
||
LIMIT $limit OFFSET $offset"
|
||
);
|
||
$stmt->execute($params);
|
||
$list = $stmt->fetchAll();
|
||
|
||
Response::success(['list' => $list, 'total' => $total, 'page' => $page, 'limit' => $limit]);
|
||
}
|
||
|
||
Response::error('未知操作', 400);
|