mzl92
eea13a2a58
# Conflicts: # app/command/InquiryImportFbCommand.php # app/inquiry/utils/FbLeadHeaderMapper.php
259 lines
7.7 KiB
PHP
259 lines
7.7 KiB
PHP
<?php
|
||
|
||
namespace app\command;
|
||
|
||
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;
|
||
use PhpOffice\PhpSpreadsheet\IOFactory;
|
||
use app\inquiry\utils\FbLeadHeaderMapper;
|
||
use support\exception\BusinessException;
|
||
|
||
/**
|
||
* 从Excel文件导入fb_leads
|
||
*/
|
||
class InquiryImportFbCommand extends Command
|
||
{
|
||
protected static $defaultName = 'iq-fb';
|
||
protected static $defaultDescription = '导入 Facebook Leads Excel 数据';
|
||
|
||
protected function configure(): void
|
||
{
|
||
$this
|
||
->addOption(
|
||
'file',
|
||
null,
|
||
InputOption::VALUE_OPTIONAL,
|
||
'要导入的单个 Excel 文件名（相对 public/excel 的路径，或绝对路径）'
|
||
)
|
||
->addOption(
|
||
'dir',
|
||
null,
|
||
InputOption::VALUE_OPTIONAL,
|
||
'Excel 目录（相对 public/ 的路径）',
|
||
'excel' // 默认就是 public/excel
|
||
);
|
||
}
|
||
|
||
protected function execute(InputInterface $input, OutputInterface $output): int
|
||
{
|
||
$fileOption = $input->getOption('file');
|
||
$dirOption = $input->getOption('dir') ?? 'excel';
|
||
|
||
// 基准目录：public/{$dirOption}
|
||
$baseDir = rtrim(public_path($dirOption), DIRECTORY_SEPARATOR);
|
||
|
||
if ($fileOption) {
|
||
// 如果传的是绝对路径，直接用；否则认为是相对 baseDir 的文件名
|
||
if ($this->isAbsolutePath($fileOption)) {
|
||
$filePath = $fileOption;
|
||
} else {
|
||
$filePath = $baseDir . DIRECTORY_SEPARATOR . $fileOption;
|
||
}
|
||
|
||
$output->writeln("<info>开始导入单个文件：</info> {$filePath}");
|
||
|
||
$this->importOneFile($filePath, $output);
|
||
} else {
|
||
// 不指定 file 时，默认导入目录下所有 .xlsx
|
||
if (!is_dir($baseDir)) {
|
||
throw new BusinessException("目录不存在：{$baseDir}");
|
||
}
|
||
|
||
$pattern = $baseDir . DIRECTORY_SEPARATOR . '*.xlsx';
|
||
$files = glob($pattern);
|
||
|
||
if (empty($files)) {
|
||
$output->writeln("<comment>目录 {$baseDir} 下没有找到 .xlsx 文件</comment>");
|
||
return self::SUCCESS;
|
||
}
|
||
|
||
$output->writeln("<info>开始批量导入目录：</info> {$baseDir}");
|
||
foreach ($files as $filePath) {
|
||
$output->writeln(" -> 文件：{$filePath}");
|
||
try {
|
||
$this->importOneFile($filePath, $output);
|
||
$output->writeln(" <info>导入成功</info>");
|
||
} catch (\Throwable $e) {
|
||
// 某个文件失败，不影响后面的文件
|
||
$output->writeln(" <error>导入失败：{$e->getMessage()}</error>");
|
||
}
|
||
}
|
||
}
|
||
|
||
return self::SUCCESS;
|
||
}
|
||
|
||
/**
|
||
* 导入单个 Excel 文件
|
||
*/
|
||
protected function importOneFile(string $filePath, OutputInterface $output): void
|
||
{
|
||
if (!is_file($filePath)) {
|
||
throw new BusinessException("文件不存在：{$filePath}");
|
||
}
|
||
|
||
$spreadsheet = IOFactory::load($filePath);
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
$rows = $sheet->toArray(null, true, true, true);
|
||
|
||
if (empty($rows)) {
|
||
throw new BusinessException('Excel 内容为空：' . $filePath);
|
||
}
|
||
|
||
// 取第一行表头
|
||
$headerRow = array_shift($rows);
|
||
|
||
// 表头映射器
|
||
$mapper = new FbLeadHeaderMapper();
|
||
|
||
// 如果有未知表头，这里会直接抛异常中断当前文件
|
||
$colToField = $mapper->buildColumnFieldMap($headerRow);
|
||
|
||
$insertData = [];
|
||
$now = time();
|
||
|
||
foreach ($rows as $row) {
|
||
$item = [];
|
||
|
||
foreach ($colToField as $colIndex => $field) {
|
||
$raw = $row[$colIndex] ?? null;
|
||
|
||
switch ($field) {
|
||
// 1) is_organic: Excel 里是 true/false，这里转成 0/1
|
||
case 'is_organic':
|
||
$item['is_organic'] = $this->normalizeIsOrganic($raw);
|
||
break;
|
||
|
||
// 2) fb_created_time: 统一转成 Y-m-d H:i:s
|
||
case 'fb_created_time':
|
||
$item['fb_created_time'] = $this->normalizeFbCreatedTime($raw);
|
||
break;
|
||
|
||
default:
|
||
if (is_string($raw)) {
|
||
$raw = trim($raw);
|
||
}
|
||
$item[$field] = $raw;
|
||
break;
|
||
}
|
||
}
|
||
|
||
// 补充公共字段
|
||
$item['source_file'] = basename($filePath);
|
||
$item['created_at'] = $now;
|
||
$item['updated_at'] = $now;
|
||
|
||
// 整行都空就跳过
|
||
$allNull = true;
|
||
foreach ($item as $v) {
|
||
if ($v !== null && $v !== '') {
|
||
$allNull = false;
|
||
break;
|
||
}
|
||
}
|
||
if ($allNull) {
|
||
continue;
|
||
}
|
||
|
||
$insertData[] = $item;
|
||
}
|
||
|
||
if ($insertData) {
|
||
Db::table('fb_leads')->insert($insertData);
|
||
} else {
|
||
$output->writeln(" <comment>文件 {$filePath} 没有有效数据行</comment>");
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 将 Excel 里的 is_organic 值转换为 0/1
|
||
*/
|
||
protected function normalizeIsOrganic($value): int
|
||
{
|
||
if ($value === null) {
|
||
return 0;
|
||
}
|
||
|
||
$v = strtolower(trim((string)$value));
|
||
|
||
// 认为这些是 “true”
|
||
$trueValues = ['1', 'true', 'yes', 'y'];
|
||
|
||
return in_array($v, $trueValues, true) ? 1 : 0;
|
||
}
|
||
|
||
/**
|
||
* 统一处理 fb_created_time
|
||
* Excel 目前是类似 '12/8/25'，这里尽量转成 'Y-m-d H:i:s'
|
||
* 解析失败时返回 null
|
||
*/
|
||
protected function normalizeFbCreatedTime($value): ?string
|
||
{
|
||
if ($value === null || $value === '') {
|
||
return null;
|
||
}
|
||
|
||
if ($value instanceof \DateTimeInterface) {
|
||
return $value->format('Y-m-d H:i:s');
|
||
}
|
||
|
||
$v = trim((string)$value);
|
||
|
||
$formats = [
|
||
'Y-m-d H:i:s',
|
||
'Y-m-d',
|
||
'n/j/y', // 12/8/25
|
||
'n/j/Y',
|
||
'd/m/Y',
|
||
'd.m.Y',
|
||
];
|
||
|
||
foreach ($formats as $fmt) {
|
||
$dt = \DateTime::createFromFormat($fmt, $v);
|
||
if ($dt !== false) {
|
||
return $dt->format('Y-m-d H:i:s');
|
||
}
|
||
}
|
||
|
||
// 纯数字当时间戳试一下
|
||
if (is_numeric($v) && strlen($v) >= 9 && strlen($v) <= 11) {
|
||
$ts = (int)$v;
|
||
if ($ts > 0) {
|
||
return date('Y-m-d H:i:s', $ts);
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* 判断是否为绝对路径（简单兼容 Windows / Linux）
|
||
*/
|
||
protected function isAbsolutePath(string $path): bool
|
||
{
|
||
// Windows: X:\ 或 \\server\share
|
||
if (preg_match('/^[a-zA-Z]:\\\\/', $path) || str_starts_with($path, '\\\\')) {
|
||
return true;
|
||
}
|
||
|
||
// *nix: / 开头
|
||
return str_starts_with($path, '/');
|
||
}
|
||
}
|
||
|
||
/*
|
||
|
||
php webman tb-db
|
||
# 等价于 php webman tb-db --dir=excel
|
||
# 会遍历 public/excel/*.xlsx 逐个导入
|
||
|
||
# 相对路径（相对 public/excel）
|
||
php webman tb-db --file="BYD tang_Leads_2025-12-04_2025-12-09.xlsx"
|
||
|
||
# 或给绝对路径
|
||
php webman tb-db --file="/www/wwwroot/xxx/public/excel/BYD tang_Leads_2025-12-04_2025-12-09.xlsx"
|
||
|
||
*/ |