Files
fdesk/app/command/HesuGoogleAiInquiryImportCommand.php
mzl92 6fa80336a6 feat: 询盘导入改为公平分配——按近10天窗口实际分到数排序(三脚本共用)
新增 InquiryFairAssigner:统计 crm_customer(合塑谷歌/导入/未删/近10天)
中名单内每人的已有条数,按 少者优先 -> 最久未分到优先 -> 名单顺序 排序,
三个导入命令分配前统一调用并打印排序结果(含各人窗口内条数)。
解决:日询盘量少于人数时固定只分给名单前几人的问题;
名单增删(离职/暂停回归)自适应,跨脚本/跨天共享,无额外状态。

Co-Authored-By: Claude <noreply@anthropic.com>
2026-08-20 15:40:45 +08:00

509 lines
22 KiB
PHP
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<?php
namespace app\command;
use app\common\model\CrmCustomer;
use app\common\utils\InquiryFairAssigner;
use app\common\utils\email\InquiryDigestMail;
use app\common\utils\email\MailSend;
use app\crm\model\Country;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use support\Db;
/**
* 合塑谷歌AI客服询盘导入
*
* AI客服询盘 Excel(库外数据)-> 建 crm_customer 档案 -> 轮询分配 -> 汇总邮件。
* 固定口径:corp_id=1(合塑)、channel_id=4(合塑谷歌)、type=2(导入)、magnitude=1(银牌);
* contacts=第一个非空联系方式(WhatsApp->手机->邮箱);去重:手机/WhatsApp/邮箱任一重复即跳过。
* 未对应库字段的内容分块拼入 desc_collect(询盘信息/意向车型/其他备注/AI评估/完整对话)。
*/
class HesuGoogleAiInquiryImportCommand extends Command
{
protected static $defaultName = 'inquiry:import-hesu-google-ai';
protected static $defaultDescription = '合塑谷歌AI客服询盘导入:新询盘建档+轮询分配+汇总邮件';
private const CORP_ID = 1;
private const CHANNEL_ID = 4; // 合塑谷歌
private const CHANNEL_NAME = '合塑谷歌(AI客服)';
private const TYPE = 2; // 导入数据
private const GRADE = 0;
private const MAGNITUDE = 1; // 银牌商机
/** @var array 国家名(小写)->country_id 缓存 */
private $countryCache = [];
protected function configure()
{
$this->addOption('file', null, InputOption::VALUE_REQUIRED, 'Excel 文件路径', 'docs/AI客服询盘_2026-08-20.xlsx')
->addOption('users', null, InputOption::VALUE_REQUIRED, '目标 user_id 列表,逗号分隔(必填)', '')
->addOption('dry-run', null, InputOption::VALUE_NONE, '只解析+去重预检+分配预演,不写库不发邮件')
->addOption('no-notify', null, InputOption::VALUE_NONE, '只建档分配,不发邮件')
->addOption('notify-only', null, InputOption::VALUE_NONE, '跳过写库,按 manifest 补发未成功的邮件')
->addOption('only-user', null, InputOption::VALUE_REQUIRED, '仅给指定 user_id 发邮件(试发)', '0');
}
/**
* 解析 Excel(按表头名定位列,顺序无关)
*/
private function parseExcel(string $filePath): array
{
$sheet = IOFactory::load($filePath)->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$colMap = [];
foreach (range('A', 'Z') as $col) {
$v = trim((string)($sheet->getCell($col . '1')->getValue() ?? ''));
if ($v !== '') {
$colMap[$v] = $col;
}
}
foreach (['手机', 'WhatsApp', '邮箱'] as $need) {
if (!isset($colMap[$need])) {
throw new \RuntimeException('Excel 缺少必需列头: ' . $need . ';实际列头: ' . implode(',', array_keys($colMap)));
}
}
$get = function (string $header, int $r) use ($sheet, $colMap): string {
if (!isset($colMap[$header])) {
return '';
}
return trim((string)($sheet->getCell($colMap[$header] . $r)->getValue() ?? ''));
};
$rows = [];
for ($r = 2; $r <= $highestRow; $r++) {
$row = [
'excel_row' => $r,
'phone' => $this->normalizeNumber($get('手机', $r)),
'whatsapp' => $this->normalizeNumber($get('WhatsApp', $r)),
'email' => $get('邮箱', $r),
'wechat' => $get('微信', $r),
'intent' => $get('意向', $r),
'country' => $get('目标国家', $r),
'budget' => $get('预算', $r),
'brand' => $get('意向品牌', $r),
'model' => $get('意向车型', $r),
'qty' => $get('采购数量', $r),
'usage' => $get('采购用途', $r),
'note' => $get('其他备注', $r),
'assess' => $get('评估理由', $r),
'dialog' => $get('完整对话记录', $r),
];
if ($row['phone'] === '' && $row['whatsapp'] === '' && $row['email'] === ''
&& $row['wechat'] === '' && $row['dialog'] === '') {
continue; // 整行为空
}
// contacts:第一个非空联系方式
$row['contacts'] = $row['whatsapp'] !== '' ? $row['whatsapp']
: ($row['phone'] !== '' ? $row['phone'] : $row['email']);
$row['intended'] = trim($row['brand'] . ' ' . $row['model']);
$rows[] = $row;
}
return $rows;
}
/**
* 号码归一化:仅保留数字与开头 +(去空格/横线等)
*/
private function normalizeNumber(string $raw): string
{
if ($raw === '') {
return '';
}
$n = preg_replace('/[^0-9+]/', '', trim($raw));
$n = preg_replace('/\++/', '+', $n);
if ($n !== '' && $n[0] !== '+') {
$n = '+' . $n;
}
return $n !== '+' ? $n : '';
}
/**
* 国家名(英文/中文,忽略大小写)匹配 country_id;匹配不上返回 0
*/
private function matchCountryId(string $name): int
{
if ($name === '') {
return 0;
}
$key = strtolower($name);
if (!array_key_exists($key, $this->countryCache)) {
$lower = strtolower($name);
$id = Country::where(function ($q) use ($lower) {
$q->whereRaw('LOWER(en_addr) = ?', [$lower])
->orWhereRaw('LOWER(cn_addr) = ?', [$lower]);
})->value('id');
$this->countryCache[$key] = (int)($id ?? 0);
}
return $this->countryCache[$key];
}
/**
* 未对应库字段的内容 -> 分块 HTML(desc_collect 富文本)
*/
private function buildDescCollect(array $row): string
{
$e = fn($v) => htmlspecialchars((string)$v, ENT_QUOTES);
$blocks = [];
$info = [];
if ($row['intent'] !== '') {
$info[] = '意向:' . $e($row['intent']);
}
if ($row['budget'] !== '') {
$info[] = '预算:' . $e($row['budget']);
}
if ($row['qty'] !== '') {
$info[] = '采购数量:' . $e($row['qty']);
}
if ($row['usage'] !== '') {
$info[] = '采购用途:' . $e($row['usage']);
}
if ($row['country'] !== '') {
$info[] = '目标国家:' . $e($row['country']);
}
if ($row['wechat'] !== '') {
$info[] = '微信:' . $e($row['wechat']);
}
if ($info) {
$blocks[] = $this->block('询盘信息', implode('<br>', $info));
}
if ($row['brand'] !== '' || $row['model'] !== '') {
$blocks[] = $this->block('意向车型',
($row['brand'] !== '' ? '品牌:' . $e($row['brand']) : '')
. ($row['brand'] !== '' && $row['model'] !== '' ? '<br>' : '')
. ($row['model'] !== '' ? '车型:' . $e($row['model']) : ''));
}
if ($row['note'] !== '') {
$blocks[] = $this->block('其他备注', $e($row['note']));
}
$ai = [];
if ($row['intent'] !== '') {
$ai[] = '意向等级:' . $e($row['intent']);
}
if ($row['assess'] !== '') {
$ai[] = '评估理由:' . $e($row['assess']);
}
if ($ai) {
$blocks[] = $this->block('AI评估', implode('<br>', $ai));
}
if ($row['dialog'] !== '') {
$blocks[] = $this->block('完整对话',
'<div style="white-space:pre-wrap;background:#f7f8fa;border:1px solid #eee;'
. 'border-radius:4px;padding:8px;font-size:12px;line-height:1.6;">' . $e($row['dialog']) . '</div>');
}
return implode('', $blocks);
}
private function block(string $title, string $bodyHtml): string
{
return '<div style="margin:0 0 10px;">'
. '<div style="font-weight:700;color:#BB0010;border-left:3px solid #BB0010;'
. 'padding-left:6px;margin-bottom:4px;">【' . htmlspecialchars($title) . '】</div>'
. '<div style="color:#333;">' . $bodyHtml . '</div></div>';
}
/**
* 去重:手机/WhatsApp/邮箱任一与库内对应列重复(含文件内)即跳过
*/
private function dedup(array $rows, OutputInterface $output): array
{
$dbSets = [];
foreach (['phone' => 'phone', 'whatsapp' => 'WhatsApp', 'email' => 'email'] as $field => $col) {
$vals = array_values(array_unique(array_filter(array_column($rows, $field))));
$set = [];
foreach (array_chunk($vals, 500) as $chunk) {
foreach (Db::table('crm_customer')->whereNull('deleted_at')->whereIn($col, $chunk)->pluck($col) as $v) {
$v = trim((string)$v);
if ($v !== '') {
if ($col === 'email') {
$set[strtolower($v)] = 1;
} else {
$set[$this->normalizeNumber($v)] = 1;
}
}
}
}
$dbSets[$field] = $set;
}
$keep = []; $skipped = []; $noContact = [];
$seen = ['phone' => [], 'whatsapp' => [], 'email' => []];
foreach ($rows as $row) {
if ($row['phone'] === '' && $row['whatsapp'] === '' && $row['email'] === '') {
$noContact[] = $row;
continue;
}
$dupReason = '';
foreach (['phone', 'whatsapp', 'email'] as $field) {
if ($row[$field] === '') {
continue;
}
$key = $field === 'email' ? strtolower($row[$field]) : $row[$field];
if (isset($dbSets[$field][$key]) || isset($seen[$field][$key])) {
$dupReason = $field . '重复: ' . $row[$field];
break;
}
}
if ($dupReason !== '') {
$skipped[] = [$row['excel_row'], $dupReason];
continue;
}
foreach (['phone', 'whatsapp', 'email'] as $field) {
if ($row[$field] !== '') {
$key = $field === 'email' ? strtolower($row[$field]) : $row[$field];
$seen[$field][$key] = 1;
}
}
$keep[] = $row;
}
return [$keep, $skipped, $noContact];
}
private function manifestPath(string $date): string
{
return runtime_path() . '/inquiry_hesu_google_ai_' . $date . '.json';
}
protected function execute(InputInterface $input, OutputInterface $output): int
{
$file = $input->getOption('file');
$targetUserIds = array_values(array_filter(array_map('intval', explode(',', (string)$input->getOption('users')))));
$dryRun = (bool)$input->getOption('dry-run');
$notifyOnly = (bool)$input->getOption('notify-only');
$noNotify = (bool)$input->getOption('no-notify');
$onlyUser = (int)$input->getOption('only-user');
$date = date('Y-m-d');
if (!$targetUserIds) {
$output->writeln('<error>--users 不能为空(目标业务员 user_id 列表)</error>');
return self::FAILURE;
}
if (!is_file($file)) {
$output->writeln('<error>文件不存在: ' . $file . '</error>');
return self::FAILURE;
}
if ($notifyOnly && $noNotify) {
$output->writeln('<error>--notify-only 与 --no-notify 不能同时使用</error>');
return self::FAILURE;
}
$output->writeln('<info>解析 Excel: ' . $file . '</info>');
$rows = $this->parseExcel($file);
$output->writeln('<info>数据行: ' . count($rows) . '</info>');
[$keep, $skipped, $noContact] = $this->dedup($rows, $output);
$output->writeln('<info>去重后待建档: ' . count($keep) . ',重复跳过: ' . count($skipped) . ',无联系方式跳过: ' . count($noContact) . '</info>');
foreach (array_slice($skipped, 0, 10) as $s) {
$output->writeln('<comment> 跳过 行' . $s[0] . ' ' . $s[1] . '</comment>');
}
if (count($skipped) > 10) {
$output->writeln('<comment> ...另 ' . (count($skipped) - 10) . ' 条</comment>');
}
foreach (array_slice($noContact, 0, 5) as $row) {
$output->writeln('<comment> 无联系方式 行' . $row['excel_row'] . ' 备注:' . mb_substr($row['note'], 0, 30) . '</comment>');
}
// 国家匹配预览
$countryMiss = [];
foreach ($keep as $row) {
if ($row['country'] !== '' && $this->matchCountryId($row['country']) === 0) {
$countryMiss[$row['country']] = 1;
}
}
if ($countryMiss) {
$output->writeln('<comment>未匹配到国家(将留空): ' . implode('、', array_keys($countryMiss)) . '</comment>');
}
// 公平分配:按近10天窗口内实际分到数升序(少者优先、最久未分到优先),再按序轮询
$fair = InquiryFairAssigner::fairOrder($targetUserIds);
$orderedUsers = $fair['ordered'];
$output->writeln('<info>分配顺序(近' . $fair['window_days'] . '天已有条数):'
. implode('、', array_map(fn($u) => $u . '(' . ($fair['counts'][$u] ?? 0) . ')', $orderedUsers)) . '</info>');
$n = count($orderedUsers);
foreach ($keep as $i => &$row) {
$row['new_user_id'] = $orderedUsers[$i % $n];
}
unset($row);
$groups = [];
foreach ($targetUserIds as $uid) {
$groups[$uid] = [];
}
foreach ($keep as $row) {
$groups[$row['new_user_id']][] = $row;
}
foreach ($targetUserIds as $uid) {
$output->writeln('<info>user ' . $uid . ' 分配: ' . count($groups[$uid]) . ' 条</info>');
}
foreach (array_slice($keep, 0, 3) as $row) {
$output->writeln('<comment>样例[行' . $row['excel_row'] . '] ' . $row['contacts'] . ' '
. mb_substr($row['intended'], 0, 24) . ' -> user ' . $row['new_user_id'] . '</comment>');
}
if ($dryRun) {
$output->writeln('<info>[dry-run] 不写库、不发邮件。确认后去掉 --dry-run 执行。</info>');
return self::SUCCESS;
}
$manifestPath = $this->manifestPath($date);
if ($notifyOnly) {
// 建档后联系方式已入库,重新解析会被去重清空——补发必须用 manifest 保存的分配结果
$manifest = is_file($manifestPath) ? (json_decode((string)@file_get_contents($manifestPath), true) ?: []) : [];
if (empty($manifest['groups'])) {
$output->writeln('<error>manifest 不存在或无分配数据: ' . $manifestPath . ';请先执行 --no-notify 或完整命令生成</error>');
return self::FAILURE;
}
$digestGroups = $manifest['groups'];
$output->writeln('<comment>[notify-only] 跳过写库,按 manifest 分配结果补发。</comment>');
} else {
$created = [];
Db::connection()->transaction(function () use ($keep, $output, &$created) {
foreach ($keep as $row) {
$c = new CrmCustomer();
$c->corp_id = self::CORP_ID;
$c->user_id = $row['new_user_id'];
$c->type = self::TYPE;
$c->channel_id = self::CHANNEL_ID;
$c->grade = self::GRADE;
$c->magnitude = self::MAGNITUDE;
$c->email = $row['email'];
$c->phone = $row['phone'];
$c->WhatsApp = $row['whatsapp'];
$c->contacts = $row['contacts'];
$c->country_id = $this->matchCountryId($row['country']) ?: null;
$c->intended_vehicles = $row['intended'] !== '' ? $row['intended'] : null;
$c->desc_collect = $this->buildDescCollect($row);
$c->obtain = time();
if (!$c->save()) {
throw new \RuntimeException('行' . $row['excel_row'] . ' 建档失败');
}
$row['code'] = $c->code;
$created[] = $row;
}
$output->writeln('<info>已建档并分配: ' . count($created) . ' 条(code 自动生成)</info>');
});
if ($created) {
$digestGroups = [];
foreach ($targetUserIds as $uid) {
$digestGroups[(string)$uid] = [];
}
foreach ($created as $row) {
$digestGroups[(string)$row['new_user_id']][] = $this->digestRow($row);
}
$this->saveManifest($manifestPath, $targetUserIds, $digestGroups);
} elseif (is_file($manifestPath)) {
$manifest = json_decode((string)file_get_contents($manifestPath), true) ?: [];
if (!empty($manifest['groups'])) {
$digestGroups = $manifest['groups'];
$output->writeln('<comment>本次无新建档(可能已导入过),沿用已有 manifest 的分配结果。</comment>');
} else {
$output->writeln('<comment>没有可建档数据,结束。</comment>');
return self::SUCCESS;
}
} else {
$output->writeln('<comment>没有可建档数据,结束。</comment>');
return self::SUCCESS;
}
if ($noNotify) {
$output->writeln('<comment>[no-notify] 已建档,跳过发邮件。核对库后用 --notify-only 补发。</comment>');
return self::SUCCESS;
}
}
$this->sendDigests($digestGroups, $targetUserIds, $manifestPath, $onlyUser, $output);
return self::SUCCESS;
}
private function saveManifest(string $path, array $targetUserIds, array $digestGroups): void
{
$perUser = []; $emailed = [];
foreach ($targetUserIds as $uid) {
$perUser[(string)$uid] = count($digestGroups[(string)$uid] ?? []);
$emailed[(string)$uid] = false;
}
$data = [
'date' => date('Y-m-d'),
'imported_at' => time(),
'target_users' => $targetUserIds,
'per_user_counts' => $perUser,
'groups' => $digestGroups,
'emailed' => $emailed,
];
file_put_contents($path, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
}
/**
* 建档行 -> 邮件汇总行
*/
private function digestRow(array $row): array
{
$remark = trim($row['intended'] . ' ' . $row['budget']);
return [
'code' => $row['code'] ?? '',
'contact' => $row['contacts'],
'email' => $row['email'],
'phone' => $row['whatsapp'] !== '' ? $row['whatsapp'] : $row['phone'],
'country_cn' => $row['country'],
'channel' => self::CHANNEL_NAME,
'remark' => mb_substr($remark, 0, 100),
];
}
private function sendDigests(array $digestGroups, array $targetUserIds, string $manifestPath, int $onlyUser, OutputInterface $output): void
{
$users = Db::table('user')->whereIn('id', $targetUserIds)->get(['id', 'name', 'email']);
$userMap = [];
foreach ($users as $u) {
$userMap[(int)$u->id] = $u;
}
$manifest = is_file($manifestPath) ? (json_decode((string)file_get_contents($manifestPath), true) ?: ['emailed' => []]) : ['emailed' => []];
$date = date('Y-m-d');
foreach ($targetUserIds as $uid) {
if (count($digestGroups[(string)$uid] ?? []) === 0) {
$output->writeln('<comment>user ' . $uid . ' 未分到询盘,不发送</comment>');
continue;
}
if ($onlyUser > 0 && $uid !== $onlyUser) {
continue;
}
if (!empty($manifest['emailed'][(string)$uid])) {
$output->writeln('<comment>跳过已发送: user ' . $uid . '</comment>');
continue;
}
$u = $userMap[$uid] ?? null;
if (!$u || empty($u->email)) {
$output->writeln('<error>user ' . $uid . ' 无邮箱,跳过</error>');
continue;
}
$inquiries = array_values($digestGroups[(string)$uid] ?? []);
$html = InquiryDigestMail::build((string)$u->name, $inquiries, $date);
$subject = 'AI客服新询盘分配汇总(共 ' . count($inquiries) . ' 条)- ' . $u->name . ' - ' . $date;
$ok = MailSend::send($u->email, $subject, $html);
if (!$ok) {
$ok = MailSend::send($u->email, $subject, $html);
}
$manifest['emailed'][(string)$uid] = (bool)$ok;
file_put_contents($manifestPath, json_encode($manifest, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
$tag = $ok ? 'info' : 'error';
$output->writeln('<' . $tag . '>user ' . $uid . ' (' . $u->email . '): '
. ($ok ? '发送成功' : '发送失败') . ',共 ' . count($inquiries) . ' 条</' . $tag . '>');
}
}
}