362 lines
13 KiB
PHP
362 lines
13 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Admin\Member;
|
|
|
|
use App\Repositories\Admin\Member\AdminMemberMarketingRepository;
|
|
use App\Support\LegacyCrypto\CiSeedCrypto;
|
|
use Illuminate\Support\Facades\Log;
|
|
|
|
final class AdminMemberMarketingService
|
|
{
|
|
public function __construct(
|
|
private readonly AdminMemberMarketingRepository $repo,
|
|
) {}
|
|
|
|
public function index(array $filters): array
|
|
{
|
|
$filters = $this->normalizeFilters($filters);
|
|
|
|
$asOfDate = $this->repo->getAsOfDate();
|
|
$total = $this->repo->countMatches($filters);
|
|
$rows = $this->repo->fetchPreview($filters, 10);
|
|
|
|
$mode = (string)($filters['login_mode'] ?? 'none');
|
|
$days = trim((string)($filters['login_days'] ?? ''));
|
|
|
|
unset($filters['inactive_days_gte'], $filters['recent_login_days_lte']); // 기존키 정리
|
|
|
|
if ($mode === 'inactive' && $days !== '') {
|
|
$filters['inactive_days_gte'] = (int)$days;
|
|
} elseif ($mode === 'recent' && $days !== '') {
|
|
$filters['recent_login_days_lte'] = (int)$days;
|
|
} else {
|
|
// none or invalid
|
|
$filters['login_mode'] = 'none';
|
|
$filters['login_days'] = '';
|
|
}
|
|
|
|
foreach ($rows as &$r) {
|
|
$plain = $this->plainPhone((string)($r['cell_phone'] ?? ''));
|
|
$r['phone_plain'] = $plain;
|
|
$r['phone_display'] = $this->formatPhone($plain);
|
|
|
|
$s3 = (string)($r['stat_3'] ?? '');
|
|
$r['stat_3_label'] = $this->stat3Map()[$s3] ?? $s3;
|
|
}
|
|
unset($r);
|
|
|
|
return [
|
|
'filters' => $filters,
|
|
'asOfDate' => $asOfDate,
|
|
'total' => $total,
|
|
'rows' => $rows,
|
|
|
|
'stat3Map' => $this->stat3Map(),
|
|
|
|
// 라디오용
|
|
'yesnoAll' => ['all'=>'전체', '1'=>'예', '0'=>'아니오'],
|
|
'agreeAll' => ['all'=>'전체', '1'=>'동의', '0'=>'미동의'],
|
|
'recentMap' => ['all'=>'전체', '30'=>'최근 30일 구매', '90'=>'최근 90일 구매'],
|
|
'qfMap' => [
|
|
'all' => '통합',
|
|
'mem_no' => '회원번호',
|
|
'name' => '성명',
|
|
'email' => '이메일',
|
|
'phone' => '전화번호',
|
|
],
|
|
];
|
|
}
|
|
|
|
public function exportZip(array $filters, string $zipPassword): array
|
|
{
|
|
$filters = $this->normalizeFilters($filters);
|
|
|
|
$asOfDate = $this->repo->getAsOfDate() ?: now()->format('Y-m-d');
|
|
$ts = now()->format('Ymd_His');
|
|
|
|
$tmpDir = storage_path('app/tmp/marketing');
|
|
if (!is_dir($tmpDir)) @mkdir($tmpDir, 0775, true);
|
|
|
|
$baseName = "marketing_members_{$asOfDate}_{$ts}";
|
|
$xlsxPath = "{$tmpDir}/{$baseName}.xlsx";
|
|
$csvPath = "{$tmpDir}/{$baseName}.csv";
|
|
$zipPath = "{$tmpDir}/{$baseName}.zip";
|
|
|
|
$useXlsx = class_exists(\PhpOffice\PhpSpreadsheet\Spreadsheet::class);
|
|
|
|
try {
|
|
if ($useXlsx) {
|
|
$this->buildXlsx($filters, $xlsxPath);
|
|
$this->makeEncryptedZip($zipPath, $zipPassword, $xlsxPath, basename($xlsxPath));
|
|
@unlink($xlsxPath);
|
|
} else {
|
|
$this->buildCsv($filters, $csvPath);
|
|
$this->makeEncryptedZip($zipPath, $zipPassword, $csvPath, basename($csvPath));
|
|
@unlink($csvPath);
|
|
}
|
|
|
|
return [
|
|
'ok' => true,
|
|
'zip_path' => $zipPath,
|
|
'download_name' => basename($zipPath),
|
|
];
|
|
} catch (\Throwable $e) {
|
|
Log::error('[marketing-export] failed', ['err' => $e->getMessage()]);
|
|
@unlink($xlsxPath); @unlink($csvPath); @unlink($zipPath);
|
|
return ['ok' => false, 'message' => '파일 생성 중 오류가 발생했습니다. (서버 로그 확인)'];
|
|
}
|
|
}
|
|
|
|
private function normalizeFilters(array $filters): array
|
|
{
|
|
$filters['qf'] = (string)($filters['qf'] ?? 'all');
|
|
$filters['q'] = trim((string)($filters['q'] ?? ''));
|
|
|
|
if ($filters['q'] !== '') {
|
|
$digits = preg_replace('/\D+/', '', (string)$filters['q']) ?? '';
|
|
if (preg_match('/^\d{10,11}$/', $digits)) {
|
|
$phone = $this->normalizeKrPhone($digits);
|
|
if ($phone !== '') $filters['phone_enc'] = $this->encryptPhone($phone);
|
|
}
|
|
}
|
|
|
|
foreach (['optin_sms','optin_email','has_phone','has_email','has_purchase','recent_purchase'] as $k) {
|
|
$filters[$k] = (string)($filters[$k] ?? 'all');
|
|
}
|
|
|
|
$filters['stat_3'] = trim((string)($filters['stat_3'] ?? ''));
|
|
$filters['reg_from'] = trim((string)($filters['reg_from'] ?? ''));
|
|
$filters['reg_to'] = trim((string)($filters['reg_to'] ?? ''));
|
|
|
|
$filters['no_login'] = (string)($filters['no_login'] ?? '0');
|
|
$filters['inactive_days_gte'] = ($filters['inactive_days_gte'] ?? '') !== '' ? (int)$filters['inactive_days_gte'] : null;
|
|
|
|
$filters['min_purchase_count'] = ($filters['min_purchase_count'] ?? '') !== '' ? (int)$filters['min_purchase_count'] : null;
|
|
$filters['min_purchase_amount'] = ($filters['min_purchase_amount'] ?? '') !== '' ? (int)$filters['min_purchase_amount'] : null;
|
|
|
|
return $filters;
|
|
}
|
|
|
|
// -------------------------
|
|
// 다운로드 헤더(한글)
|
|
// -------------------------
|
|
private function headersKorean(): array
|
|
{
|
|
return [
|
|
'회원번호',
|
|
'성명',
|
|
'전화번호',
|
|
'이메일',
|
|
|
|
'로그인상태',
|
|
'가입일시',
|
|
'최근로그인일시',
|
|
'미접속일수',
|
|
|
|
'SMS수신동의',
|
|
'이메일수신동의',
|
|
'구매여부',
|
|
|
|
'누적구매횟수',
|
|
'누적구매금액',
|
|
'최근30일구매횟수',
|
|
'최근30일구매금액',
|
|
'최근90일구매횟수',
|
|
'최근90일구매금액',
|
|
'최근구매일시',
|
|
|
|
'통계기준일',
|
|
];
|
|
}
|
|
|
|
private function buildCsv(array $filters, string $path): void
|
|
{
|
|
$fp = fopen($path, 'w');
|
|
if (!$fp) throw new \RuntimeException('cannot open csv');
|
|
|
|
// Excel 한글 깨짐 방지 BOM
|
|
fwrite($fp, "\xEF\xBB\xBF");
|
|
fputcsv($fp, $this->headersKorean());
|
|
|
|
$seed = app(CiSeedCrypto::class);
|
|
$statMap = $this->stat3Map();
|
|
|
|
$this->repo->chunkExport($filters, 2000, function (array $rows) use ($fp, $seed, $statMap) {
|
|
foreach ($rows as $r) {
|
|
$phonePlain = $this->plainPhone((string)($r['cell_phone'] ?? ''), $seed);
|
|
|
|
$pcTotal = (int)($r['purchase_count_total'] ?? 0);
|
|
$hasPurchase = $pcTotal > 0 ? '있음' : '없음';
|
|
|
|
$s3 = (string)($r['stat_3'] ?? '');
|
|
$s3Label = $statMap[$s3] ?? $s3;
|
|
|
|
fputcsv($fp, [
|
|
(int)$r['mem_no'],
|
|
(string)($r['name'] ?? ''),
|
|
$phonePlain, // 마케팅 발송용: 숫자만
|
|
(string)($r['email'] ?? ''),
|
|
|
|
$s3Label,
|
|
(string)($r['reg_at'] ?? ''),
|
|
(string)($r['last_login_at'] ?? ''),
|
|
($r['days_since_login'] === null ? '' : (int)$r['days_since_login']),
|
|
|
|
((int)($r['optin_sms'] ?? 0) === 1 ? '동의' : '미동의'),
|
|
((int)($r['optin_email'] ?? 0) === 1 ? '동의' : '미동의'),
|
|
$hasPurchase,
|
|
|
|
$pcTotal,
|
|
(int)($r['purchase_amount_total'] ?? 0),
|
|
(int)($r['purchase_count_30d'] ?? 0),
|
|
(int)($r['purchase_amount_30d'] ?? 0),
|
|
(int)($r['purchase_count_90d'] ?? 0),
|
|
(int)($r['purchase_amount_90d'] ?? 0),
|
|
(string)($r['last_purchase_at'] ?? ''),
|
|
|
|
(string)($r['as_of_date'] ?? ''),
|
|
]);
|
|
}
|
|
});
|
|
|
|
fclose($fp);
|
|
}
|
|
|
|
private function buildXlsx(array $filters, string $path): void
|
|
{
|
|
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('회원추출');
|
|
|
|
$headers = $this->headersKorean();
|
|
foreach ($headers as $i => $h) {
|
|
$sheet->setCellValueByColumnAndRow($i + 1, 1, $h);
|
|
}
|
|
|
|
$rowNo = 2;
|
|
$seed = app(CiSeedCrypto::class);
|
|
$statMap = $this->stat3Map();
|
|
|
|
$this->repo->chunkExport($filters, 2000, function (array $rows) use ($sheet, &$rowNo, $seed, $statMap) {
|
|
foreach ($rows as $r) {
|
|
$phonePlain = $this->plainPhone((string)($r['cell_phone'] ?? ''), $seed);
|
|
|
|
$pcTotal = (int)($r['purchase_count_total'] ?? 0);
|
|
$hasPurchase = $pcTotal > 0 ? '있음' : '없음';
|
|
|
|
$s3 = (string)($r['stat_3'] ?? '');
|
|
$s3Label = $statMap[$s3] ?? $s3;
|
|
|
|
$values = [
|
|
(int)$r['mem_no'],
|
|
(string)($r['name'] ?? ''),
|
|
$phonePlain,
|
|
(string)($r['email'] ?? ''),
|
|
|
|
$s3Label,
|
|
(string)($r['reg_at'] ?? ''),
|
|
(string)($r['last_login_at'] ?? ''),
|
|
($r['days_since_login'] === null ? '' : (int)$r['days_since_login']),
|
|
|
|
((int)($r['optin_sms'] ?? 0) === 1 ? '동의' : '미동의'),
|
|
((int)($r['optin_email'] ?? 0) === 1 ? '동의' : '미동의'),
|
|
$hasPurchase,
|
|
|
|
$pcTotal,
|
|
(int)($r['purchase_amount_total'] ?? 0),
|
|
(int)($r['purchase_count_30d'] ?? 0),
|
|
(int)($r['purchase_amount_30d'] ?? 0),
|
|
(int)($r['purchase_count_90d'] ?? 0),
|
|
(int)($r['purchase_amount_90d'] ?? 0),
|
|
(string)($r['last_purchase_at'] ?? ''),
|
|
|
|
(string)($r['as_of_date'] ?? ''),
|
|
];
|
|
|
|
foreach ($values as $i => $v) {
|
|
$sheet->setCellValueByColumnAndRow($i + 1, $rowNo, $v);
|
|
}
|
|
$rowNo++;
|
|
}
|
|
});
|
|
|
|
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
|
|
$writer->save($path);
|
|
}
|
|
|
|
private function makeEncryptedZip(string $zipPath, string $password, string $filePath, string $innerName): void
|
|
{
|
|
if (!class_exists(\ZipArchive::class)) throw new \RuntimeException('ZipArchive not available');
|
|
|
|
$zip = new \ZipArchive();
|
|
if ($zip->open($zipPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
|
|
throw new \RuntimeException('cannot open zip');
|
|
}
|
|
|
|
$zip->setPassword($password);
|
|
$zip->addFile($filePath, $innerName);
|
|
|
|
if (method_exists($zip, 'setEncryptionName')) {
|
|
$method = defined('\ZipArchive::EM_AES_256') ? \ZipArchive::EM_AES_256 : \ZipArchive::EM_TRAD_PKWARE;
|
|
$zip->setEncryptionName($innerName, $method, $password);
|
|
}
|
|
|
|
$zip->close();
|
|
}
|
|
|
|
// -------------------------
|
|
// Maps / helpers
|
|
// -------------------------
|
|
private function stat3Map(): array
|
|
{
|
|
return [
|
|
'1' => '로그인정상',
|
|
/*'2' => '2. 로그인만가능',
|
|
'3' => '3. 로그인불가 (접근금지)',
|
|
'4' => '4. 탈퇴완료(아이디보관)',
|
|
'5' => '5. 탈퇴완료',*/
|
|
'6' => '휴면회원',
|
|
];
|
|
}
|
|
|
|
private function normalizeKrPhone(string $raw): string
|
|
{
|
|
$n = preg_replace('/\D+/', '', $raw) ?? '';
|
|
if ($n === '') return '';
|
|
if (str_starts_with($n, '82')) $n = '0'.substr($n, 2);
|
|
if (!preg_match('/^01[016789]\d{7,8}$/', $n)) return '';
|
|
return $n;
|
|
}
|
|
|
|
private function encryptPhone(string $raw010): string
|
|
{
|
|
$seed = app(CiSeedCrypto::class);
|
|
return (string) $seed->encrypt($raw010);
|
|
}
|
|
|
|
private function plainPhone(string $cellPhoneCol, ?CiSeedCrypto $seed = null): string
|
|
{
|
|
$v = trim($cellPhoneCol);
|
|
if ($v === '') return '';
|
|
|
|
$digits = preg_replace('/\D+/', '', $v) ?? '';
|
|
if (preg_match('/^\d{10,11}$/', $digits)) return $digits;
|
|
|
|
try {
|
|
$seed = $seed ?: app(CiSeedCrypto::class);
|
|
$plain = (string) $seed->decrypt($v);
|
|
$plainDigits = preg_replace('/\D+/', '', $plain) ?? '';
|
|
if (preg_match('/^\d{10,11}$/', $plainDigits)) return $plainDigits;
|
|
} catch (\Throwable $e) {}
|
|
|
|
return '';
|
|
}
|
|
|
|
private function formatPhone(string $digits): string
|
|
{
|
|
if (!preg_match('/^\d{10,11}$/', $digits)) return '-';
|
|
if (strlen($digits) === 11) return substr($digits,0,3).'-'.substr($digits,3,4).'-'.substr($digits,7,4);
|
|
return substr($digits,0,3).'-'.substr($digits,3,3).'-'.substr($digits,6,4);
|
|
}
|
|
}
|