144 lines
4.2 KiB
PHP
144 lines
4.2 KiB
PHP
<?php
|
||
namespace app\command;
|
||
|
||
use support\Db;
|
||
use Symfony\Component\Console\Command\Command;
|
||
use Symfony\Component\Console\Input\InputInterface;
|
||
use Symfony\Component\Console\Output\OutputInterface;
|
||
|
||
/**
|
||
* 将拉取邮箱的详情进行清洗，找出询盘的邮件入库（根据emai_detail）
|
||
*/
|
||
class EmailParseCommand extends Command
|
||
{
|
||
protected static $defaultName = 'email-parse';
|
||
protected static $defaultDescription = '邮件清洗';
|
||
|
||
protected function configure()
|
||
{
|
||
// 不再接收参数，固定每次 100 条
|
||
}
|
||
|
||
protected function execute(InputInterface $input, OutputInterface $output): int
|
||
{
|
||
$batchSize = 100; // 每次处理 100 条
|
||
$page = 0;
|
||
|
||
// 明确定义 parsed_contacts 表里要插入的所有列（顺序无所谓，但要全部包含）
|
||
$fields = [
|
||
'email_id',
|
||
'date',
|
||
'email',
|
||
'name',
|
||
'tel',
|
||
'company',
|
||
'country',
|
||
'form_url',
|
||
'message',
|
||
'ip',
|
||
'iplocation',
|
||
'ilang',
|
||
'mailto',
|
||
'navigator_language',
|
||
'navigator_platform',
|
||
'screen_width',
|
||
'screen_height',
|
||
'ucountry',
|
||
];
|
||
|
||
while (true) {
|
||
$rows = Db::table('emails')
|
||
->orderBy('id', 'asc')
|
||
->limit($batchSize)
|
||
->offset($page * $batchSize)
|
||
->get();
|
||
|
||
if ($rows->isEmpty()) {
|
||
break;
|
||
}
|
||
|
||
$toInsert = [];
|
||
foreach ($rows as $row) {
|
||
$parsed = $this->parseHtml($row->body);
|
||
if ($parsed === null) {
|
||
$output->writeln(" ↳ [{$row->id}] skip: not a contact-form HTML");
|
||
continue;
|
||
}
|
||
|
||
// 严格按 $fields 顺序填充每条记录，缺失的字段补 null
|
||
$record = [];
|
||
foreach ($fields as $col) {
|
||
if ($col === 'email_id') {
|
||
$record[$col] = $row->id;
|
||
} else {
|
||
$record[$col] = $parsed[$col] ?? null;
|
||
}
|
||
}
|
||
|
||
$toInsert[] = $record;
|
||
}
|
||
|
||
if (!empty($toInsert)) {
|
||
Db::table('email_parsed_result')->insert($toInsert);
|
||
$output->writeln("Inserted batch #" . ($page + 1) . ", count: " . count($toInsert));
|
||
}
|
||
|
||
$page++;
|
||
}
|
||
|
||
$output->writeln("✔ All done.");
|
||
return Command::SUCCESS;
|
||
}
|
||
|
||
/**
|
||
* 解析 HTML，找不到 <h2>Preview</h2> 则返回 null，
|
||
* 否则提取所有 <span>Key:</span> Value 为小写蛇形 key=>value 数组
|
||
*/
|
||
protected function parseHtml(string $html): ?array
|
||
{
|
||
$doc = new \DOMDocument();
|
||
libxml_use_internal_errors(true);
|
||
$doc->loadHTML('<?xml encoding="utf-8"?>' . $html);
|
||
libxml_clear_errors();
|
||
|
||
$xpath = new \DOMXPath($doc);
|
||
|
||
// 类型检查
|
||
if ($xpath->query("//h2[text()='Preview']")->length === 0) {
|
||
return null;
|
||
}
|
||
|
||
$nodes = $xpath->query("//div[span]");
|
||
$data = [];
|
||
|
||
/** @var \DOMElement $node */
|
||
foreach ($nodes as $node) {
|
||
$span = $xpath->query("span", $node)->item(0);
|
||
if (! $span) {
|
||
continue;
|
||
}
|
||
|
||
// 原始键名
|
||
$key = trim(str_replace(':', '', $span->textContent));
|
||
// 转成小写蛇形
|
||
$col = strtolower(str_replace(' ', '_', $key));
|
||
|
||
// 获取值
|
||
$a = $xpath->query(".//a", $node)->item(0);
|
||
if ($a && $a->hasAttribute('href')) {
|
||
$value = trim($a->getAttribute('href'));
|
||
} else {
|
||
$clone = $node->cloneNode(true);
|
||
$spanClone = $xpath->query(".//span", $clone)->item(0);
|
||
if ($spanClone) {
|
||
$spanClone->parentNode->removeChild($spanClone);
|
||
}
|
||
$value = trim($clone->textContent);
|
||
}
|
||
|
||
$data[$col] = $value;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
} |