390 lines
14 KiB
PHP
390 lines
14 KiB
PHP
<?php
|
|
|
|
namespace App\Repositories\Mypage;
|
|
|
|
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
final class UsageRepository
|
|
{
|
|
|
|
/**
|
|
* 상품별 핀 발행(확인) 방법 조회
|
|
* - gc_products.pin_check_methods: JSON 배열
|
|
* - gc_products.is_buyback_allowed: BUYBACK 안전장치
|
|
*/
|
|
public function getProductsIssueOptions(array $productIds): array
|
|
{
|
|
$ids = array_values(array_unique(array_filter(array_map('intval', $productIds), fn($n) => $n > 0)));
|
|
if (empty($ids)) return [];
|
|
|
|
$rows = DB::table('gc_products')
|
|
->select(['id', 'pin_check_methods', 'is_buyback_allowed'])
|
|
->whereIn('id', $ids)
|
|
->get();
|
|
|
|
$out = [];
|
|
foreach ($rows as $r) {
|
|
$raw = $r->pin_check_methods ?? null;
|
|
$hasPinCheckMethods = !($raw === null || trim((string)$raw) === '');
|
|
|
|
$out[(int)$r->id] = [
|
|
'pin_check_methods' => $this->decodeJsonArray($raw),
|
|
'has_pin_check_methods' => $hasPinCheckMethods,
|
|
'is_buyback_allowed' => (bool)($r->is_buyback_allowed ?? false),
|
|
];
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
private function decodeJsonArray($json): array
|
|
{
|
|
if (is_array($json)) return $json;
|
|
|
|
$json = trim((string)$json);
|
|
if ($json === '') return [];
|
|
|
|
$decoded = json_decode($json, true);
|
|
return is_array($decoded) ? $decoded : [];
|
|
}
|
|
|
|
public function findAttemptWithOrder(int $attemptId): ?object
|
|
{
|
|
return DB::table('gc_payment_attempts as a')
|
|
->leftJoin('gc_pin_order as o', 'o.id', '=', 'a.order_id')
|
|
->select([
|
|
'a.id as attempt_id',
|
|
'a.provider as attempt_provider',
|
|
'a.oid as attempt_oid',
|
|
'a.mem_no as attempt_mem_no',
|
|
'a.order_id as attempt_order_id',
|
|
'a.pay_method as attempt_pay_method',
|
|
'a.status as attempt_status',
|
|
'a.pg_tid as attempt_pg_tid',
|
|
'a.return_code as attempt_return_code',
|
|
'a.return_msg as attempt_return_msg',
|
|
'a.request_payload as attempt_request_payload',
|
|
'a.response_payload as attempt_response_payload',
|
|
'a.return_payload as attempt_return_payload',
|
|
'a.noti_payload as attempt_noti_payload',
|
|
'a.created_at as attempt_created_at',
|
|
'a.updated_at as attempt_updated_at',
|
|
|
|
// 추가: cancel_status 필드들
|
|
'a.cancel_status as attempt_cancel_status',
|
|
'a.cancel_requested_at as attempt_cancel_requested_at',
|
|
'a.cancel_done_at as attempt_cancel_done_at',
|
|
'a.cancel_last_code as attempt_cancel_last_code',
|
|
'a.cancel_last_msg as attempt_cancel_last_msg',
|
|
|
|
'o.id as order_id',
|
|
'o.oid as order_oid',
|
|
'o.mem_no as order_mem_no',
|
|
'o.stat_pay as order_stat_pay',
|
|
'o.products_name as order_product_name',
|
|
'o.products_id as order_product_id',
|
|
'o.provider as order_provider',
|
|
'o.pay_method as order_pay_method',
|
|
'o.pg_tid as order_pg_tid',
|
|
'o.ret_code as order_ret_code',
|
|
'o.ret_msg as order_ret_msg',
|
|
'o.subtotal_amount as order_subtotal_amount',
|
|
'o.fee_amount as order_fee_amount',
|
|
'o.pg_fee_amount as order_pg_fee_amount',
|
|
'o.pay_money as order_pay_money',
|
|
'o.pay_data as order_pay_data',
|
|
'o.ret_data as order_ret_data',
|
|
'o.created_at as order_created_at',
|
|
'o.updated_at as order_updated_at',
|
|
|
|
// 추가: order cancel_status 필드들
|
|
'o.cancel_status as order_cancel_status',
|
|
'o.cancel_requested_at as order_cancel_requested_at',
|
|
'o.cancel_done_at as order_cancel_done_at',
|
|
'o.cancel_last_code as order_cancel_last_code',
|
|
'o.cancel_last_msg as order_cancel_last_msg',
|
|
'o.cancel_reason as order_cancel_reason',
|
|
])
|
|
->where('a.id', $attemptId)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* 리스트: 검색/페이징
|
|
*/
|
|
public function paginateAttemptsWithOrder(int $memNo, array $filters, int $perPage = 20): LengthAwarePaginator
|
|
{
|
|
$q = trim((string)($filters['q'] ?? ''));
|
|
$method = trim((string)($filters['method'] ?? ''));
|
|
$status = trim((string)($filters['status'] ?? ''));
|
|
$from = trim((string)($filters['from'] ?? ''));
|
|
$to = trim((string)($filters['to'] ?? ''));
|
|
|
|
// 주문아이템 집계
|
|
$oiAgg = DB::table('gc_pin_order_items')
|
|
->selectRaw('order_id, SUM(qty) as total_qty, MIN(item_name) as first_item_name')
|
|
->groupBy('order_id');
|
|
|
|
// 발행 집계
|
|
$issueAgg = DB::table('gc_pin_issues')
|
|
->selectRaw('order_id, COUNT(*) as issued_count')
|
|
->groupBy('order_id');
|
|
|
|
$qb = DB::table('gc_payment_attempts as a')
|
|
->leftJoin('gc_pin_order as o', 'o.id', '=', 'a.order_id')
|
|
->leftJoinSub($oiAgg, 'oi', 'oi.order_id', '=', 'o.id')
|
|
->leftJoinSub($issueAgg, 'gi', 'gi.order_id', '=', 'o.id')
|
|
->where('a.mem_no', $memNo)
|
|
|
|
// 리스트 기본 노출 대상
|
|
->where(function ($s) {
|
|
// 1) 취소완료
|
|
$s->where(function ($x) {
|
|
$x->where('a.cancel_status', 'success')
|
|
->orWhere('o.cancel_status', 'success');
|
|
})
|
|
|
|
// 2) 결제완료
|
|
->orWhere(function ($x) {
|
|
$x->where('a.status', 'paid')
|
|
->orWhere('o.stat_pay', 'p');
|
|
})
|
|
|
|
// 3) 입금대기(가상계좌)
|
|
->orWhere(function ($x) {
|
|
$x->where('a.status', 'issued')
|
|
->orWhere('o.stat_pay', 'w');
|
|
});
|
|
})
|
|
|
|
->select([
|
|
'a.id as attempt_id',
|
|
'o.id as order_id',
|
|
'o.oid as order_oid',
|
|
DB::raw("COALESCE(o.products_name, oi.first_item_name) as product_name"),
|
|
'oi.first_item_name as item_name',
|
|
DB::raw("COALESCE(oi.total_qty, 0) as total_qty"),
|
|
'a.pay_method as pay_method',
|
|
'o.pay_money as pay_money',
|
|
'a.status as attempt_status',
|
|
'o.stat_pay as order_stat_pay',
|
|
'a.cancel_status as attempt_cancel_status',
|
|
'o.cancel_status as order_cancel_status',
|
|
DB::raw("COALESCE(gi.issued_count, 0) as issued_count"),
|
|
'a.created_at as created_at',
|
|
])
|
|
->orderByDesc('a.id');
|
|
|
|
// 주문번호 검색
|
|
if ($q !== '') {
|
|
$qb->where(function ($w) use ($q) {
|
|
$w->where('o.oid', $q)
|
|
->orWhere('a.oid', $q);
|
|
});
|
|
}
|
|
|
|
// 결제수단
|
|
if ($method !== '') {
|
|
$qb->where('a.pay_method', $method);
|
|
}
|
|
|
|
/**
|
|
* 상태 필터는 화면 의미 기준
|
|
*
|
|
* pay : 입금/결제 진행 상태
|
|
* issue_wait : 결제완료 + 발행대기
|
|
* issue_done : 결제완료 + 발행완료
|
|
* cancelled : 결제취소
|
|
* failed : 결제실패
|
|
*
|
|
* 하위호환:
|
|
* paid : issue_wait + issue_done
|
|
* issued : pay
|
|
* cancel : cancelled
|
|
* canceled : cancelled
|
|
*/
|
|
if ($status !== '') {
|
|
if (in_array($status, ['cancel', 'cancelled', 'canceled'], true)) {
|
|
$qb->where(function ($x) {
|
|
$x->where('a.cancel_status', 'success')
|
|
->orWhere('o.cancel_status', 'success');
|
|
});
|
|
} elseif ($status === 'issue_done') {
|
|
$qb->where(function ($x) {
|
|
$x->where('a.status', 'paid')
|
|
->orWhere('o.stat_pay', 'p');
|
|
});
|
|
$qb->whereRaw('COALESCE(gi.issued_count, 0) > 0');
|
|
} elseif ($status === 'issue_wait') {
|
|
$qb->where(function ($x) {
|
|
$x->where('a.status', 'paid')
|
|
->orWhere('o.stat_pay', 'p');
|
|
});
|
|
$qb->whereRaw('COALESCE(gi.issued_count, 0) = 0');
|
|
} elseif ($status === 'pay' || $status === 'issued') {
|
|
$qb->where(function ($x) {
|
|
$x->where('a.status', 'issued')
|
|
->orWhere('o.stat_pay', 'w')
|
|
->orWhere('a.status', 'ready')
|
|
->orWhere('a.status', 'redirected');
|
|
});
|
|
} elseif ($status === 'paid') {
|
|
// 하위호환: 결제완료 전체(발행대기+발행완료)
|
|
$qb->where(function ($x) {
|
|
$x->where('a.status', 'paid')
|
|
->orWhere('o.stat_pay', 'p');
|
|
});
|
|
} elseif ($status === 'failed') {
|
|
$qb->where(function ($x) {
|
|
$x->where('a.status', 'failed')
|
|
->orWhere('o.stat_pay', 'f');
|
|
});
|
|
} else {
|
|
$qb->where('a.status', $status);
|
|
}
|
|
}
|
|
|
|
// 날짜 필터
|
|
if ($from !== '') {
|
|
$qb->whereDate('a.created_at', '>=', $from);
|
|
}
|
|
if ($to !== '') {
|
|
$qb->whereDate('a.created_at', '<=', $to);
|
|
}
|
|
|
|
return $qb->paginate($perPage)->appends($filters);
|
|
}
|
|
|
|
public function getOrderItems(int $orderId)
|
|
{
|
|
return DB::table('gc_pin_order_items')
|
|
->where('order_id', $orderId)
|
|
->orderBy('id', 'asc')
|
|
->get();
|
|
}
|
|
|
|
public function countAssignedPins(int $orderId): int
|
|
{
|
|
return (int) DB::table('gc_pins')
|
|
->where('order_id', $orderId)
|
|
->count();
|
|
}
|
|
|
|
public function getAssignedPinsStatusSummary(int $orderId): array
|
|
{
|
|
$rows = DB::table('gc_pins')
|
|
->selectRaw('status, COUNT(*) as cnt')
|
|
->where('order_id', $orderId)
|
|
->groupBy('status')
|
|
->get();
|
|
|
|
$out = [];
|
|
foreach ($rows as $r) $out[(string)$r->status] = (int)$r->cnt;
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* 핀 목록(오픈 전/후 표시용) — 핀 반납(홀드 해제)은 이번 범위 제외
|
|
*/
|
|
public function getPinsForOrder(int $orderId): array
|
|
{
|
|
$rows = DB::table('gc_pins')
|
|
->where('order_id', $orderId)
|
|
->orderBy('id', 'asc')
|
|
->get();
|
|
|
|
return array_map(fn($r) => (array)$r, $rows->all());
|
|
}
|
|
|
|
/**
|
|
* 취소 로그 조회
|
|
*/
|
|
public function getCancelLogsForAttempt(int $attemptId, int $limit = 20): array
|
|
{
|
|
$rows = DB::table('gc_payment_cancel_logs')
|
|
->where('attempt_id', $attemptId)
|
|
->orderByDesc('id')
|
|
->limit($limit)
|
|
->get();
|
|
|
|
return array_map(fn($r) => (array)$r, $rows->all());
|
|
}
|
|
|
|
public function getIssuesForOrder(int $orderId): array
|
|
{
|
|
$rows = DB::table('gc_pin_issues')
|
|
->where('order_id', $orderId)
|
|
->orderBy('id', 'asc')
|
|
->get();
|
|
|
|
return array_map(function ($r) {
|
|
$arr = (array)$r;
|
|
$arr['pins_json_decoded'] = $this->decodeJsonArray($arr['pins_json'] ?? null);
|
|
$arr['issue_logs_json_decoded'] = $this->decodeJsonArray($arr['issue_logs_json'] ?? null);
|
|
return $arr;
|
|
}, $rows->all());
|
|
}
|
|
|
|
public function getIssuesForOrderForUpdate(int $orderId): array
|
|
{
|
|
$rows = DB::table('gc_pin_issues')
|
|
->where('order_id', $orderId)
|
|
->orderBy('id', 'asc')
|
|
->lockForUpdate()
|
|
->get();
|
|
|
|
return array_map(fn ($r) => (array)$r, $rows->all());
|
|
}
|
|
|
|
/**
|
|
* 핀번호 확인 / SMS / 출금완료 등 강한 잠금
|
|
*/
|
|
public function hasLockedOrOpenedIssues(int $orderId): bool
|
|
{
|
|
return DB::table('gc_pin_issues')
|
|
->where('order_id', $orderId)
|
|
->where(function ($q) {
|
|
$q->whereNotNull('opened_at')
|
|
->orWhereNotNull('sms_sent_at')
|
|
->orWhereNotNull('payout_done_at')
|
|
->orWhere('cancel_status', 'LOCKED');
|
|
})
|
|
->exists();
|
|
}
|
|
|
|
|
|
public function hasAnyIssuedIssues(int $orderId): bool
|
|
{
|
|
return DB::table('gc_pin_issues')
|
|
->where('order_id', $orderId)
|
|
->where(function ($q) {
|
|
$q->whereIn('issue_status', ['PROCESSING', 'ISSUED'])
|
|
->orWhere('pin_count', '>', 0)
|
|
->orWhereNotNull('issued_at');
|
|
})
|
|
->exists();
|
|
}
|
|
|
|
public function markIssueOpened(int $issueId, string $openedAt, array $logs, ?string $reason = null): void
|
|
{
|
|
DB::table('gc_pin_issues')
|
|
->where('id', $issueId)
|
|
->update([
|
|
'opened_at' => $openedAt,
|
|
'cancel_status' => 'LOCKED',
|
|
'cancel_locked_reason' => $reason ?? '핀번호 확인 완료',
|
|
'issue_logs_json' => json_encode($logs, JSON_UNESCAPED_UNICODE),
|
|
'updated_at' => $openedAt,
|
|
]);
|
|
}
|
|
|
|
public function getSkuForOrderItem(int $productId, int $skuId): ?object
|
|
{
|
|
return DB::table('gc_product_skus')
|
|
->where('product_id', $productId)
|
|
->where('id', $skuId)
|
|
->where('is_active', 1)
|
|
->first();
|
|
}
|
|
}
|