511 lines
16 KiB
PHP
511 lines
16 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\Park;
|
|
use App\Models\Price;
|
|
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
|
|
use Illuminate\Database\Query\Builder;
|
|
use Illuminate\Support\Facades\Auth;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
use RuntimeException;
|
|
|
|
final class ReserveService
|
|
{
|
|
/**
|
|
* 一覧取得（検索＋ページング）
|
|
* Why: Controller を薄くするため、クエリ構築をここに集約する。
|
|
*
|
|
* @return array{list: LengthAwarePaginator, sort: string, sort_type: string, user_id: string, park_id: ?int, reserve_date_from: mixed, reserve_date_to: mixed, keyword: string, valid_flag: ?string, parkOptions: array}
|
|
*/
|
|
public function paginate(array $payload): array
|
|
{
|
|
$sort = $this->normalizeSort((string) $payload['sort'], $this->sortableKeys());
|
|
$sortType = $this->normalizeSortType((string) $payload['sortType']);
|
|
|
|
$q = $this->baseQuery();
|
|
|
|
$this->applyFilters($q, $payload);
|
|
$this->applySort($q, $sort, $sortType);
|
|
|
|
$parkOptions = Park::query()
|
|
->orderBy('park_id', 'asc')
|
|
->pluck('park_name', 'park_id')
|
|
->toArray();
|
|
|
|
$list = $q->paginate(50);
|
|
|
|
$this->attachPriceNames($list);
|
|
|
|
return [
|
|
'list' => $list,
|
|
'sort' => $sort,
|
|
'sort_type' => $sortType,
|
|
|
|
'user_id' => (string) $payload['userId'],
|
|
'park_id' => $payload['parkId'],
|
|
'reserve_date_from' => $payload['fromDt'],
|
|
'reserve_date_to' => $payload['toDt'],
|
|
'keyword' => (string) $payload['keyword'],
|
|
'valid_flag' => $payload['validFlag'],
|
|
|
|
'parkOptions' => $parkOptions,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* 新規画面用のプルダウンなど。
|
|
*/
|
|
public function getFormOptions(): array
|
|
{
|
|
$userTypes = DB::table('usertype')
|
|
->orderBy('user_categoryid', 'asc')
|
|
->get([
|
|
'user_categoryid',
|
|
'usertype_subject1',
|
|
'usertype_subject2',
|
|
'usertype_subject3',
|
|
])
|
|
->map(function (object $row): object {
|
|
$labels = array_filter([
|
|
$row->usertype_subject1,
|
|
$row->usertype_subject2,
|
|
$row->usertype_subject3,
|
|
], static fn ($v): bool => $v !== null && $v !== '');
|
|
|
|
$row->display_name = $labels ? implode('/', $labels) : '';
|
|
return $row;
|
|
});
|
|
|
|
$parks = Park::query()
|
|
->orderBy('park_id', 'asc')
|
|
->get(['park_id', 'park_name']);
|
|
|
|
$priceOptions = $this->getUnifiedPriceOptions();
|
|
|
|
return [
|
|
'userTypes' => $userTypes,
|
|
'parks' => $parks,
|
|
'priceOptions' => $priceOptions,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* 新規画面用：プルダウン等
|
|
*/
|
|
public function getCreateForm(): array
|
|
{
|
|
$userOptions = DB::table('user')
|
|
->orderBy('user_id', 'asc')
|
|
->limit(5000)
|
|
->pluck(DB::raw("concat(user_id, ' ', user_name)"), 'user_id')
|
|
->toArray();
|
|
|
|
$parkOptions = DB::table('park')
|
|
->orderBy('park_id', 'asc')
|
|
->pluck('park_name', 'park_id')
|
|
->toArray();
|
|
|
|
$userTypeOptions = DB::table('usertype')
|
|
->orderBy('user_categoryid')
|
|
->select([
|
|
'user_categoryid',
|
|
DB::raw("
|
|
COALESCE(
|
|
NULLIF(TRIM(CONCAT_WS('/',
|
|
NULLIF(TRIM(usertype_subject1), ''),
|
|
NULLIF(TRIM(usertype_subject2), ''),
|
|
NULLIF(TRIM(usertype_subject3), '')
|
|
)), ''),
|
|
print_name
|
|
) AS label
|
|
")
|
|
])
|
|
->pluck('label', 'user_categoryid')
|
|
->toArray();
|
|
|
|
$priceOptions = Schema::hasTable('price')
|
|
? DB::table('price')
|
|
->orderBy('price_parkplaceid')
|
|
->pluck('prine_name', 'price_parkplaceid')
|
|
->toArray()
|
|
: [];
|
|
|
|
$psectionOptions = Schema::hasTable('psection')
|
|
? DB::table('psection')
|
|
->orderBy('psection_id')
|
|
->pluck(
|
|
Schema::hasColumn('psection', 'psection_subject') ? 'psection_subject' : 'psection_id',
|
|
'psection_id'
|
|
)
|
|
->toArray()
|
|
: [];
|
|
|
|
$ptypeOptions = Schema::hasTable('ptype')
|
|
? DB::table('ptype')
|
|
->orderBy('ptype_id')
|
|
->pluck(
|
|
Schema::hasColumn('ptype', 'ptype_subject') ? 'ptype_subject' : 'ptype_id',
|
|
'ptype_id'
|
|
)
|
|
->toArray()
|
|
: [];
|
|
|
|
return [
|
|
'userOptions' => $userOptions,
|
|
'parkOptions' => $parkOptions,
|
|
'userTypeOptions' => $userTypeOptions,
|
|
'priceOptions' => $priceOptions,
|
|
'psectionOptions' => $psectionOptions,
|
|
'ptypeOptions' => $ptypeOptions,
|
|
];
|
|
}
|
|
|
|
|
|
/**
|
|
* 編集画面用：対象レコード＋プルダウン
|
|
*/
|
|
public function getEditForm(int $reserveId): array
|
|
{
|
|
$row = DB::table('reserve')->where('reserve_id', $reserveId)->first();
|
|
if ($row === null) {
|
|
abort(404);
|
|
}
|
|
|
|
$userOptions = DB::table('user')
|
|
->orderBy('user_id', 'asc')
|
|
->limit(5000)
|
|
->pluck(DB::raw("concat(user_id, ' ', user_name)"), 'user_id')
|
|
->toArray();
|
|
|
|
$parkOptions = DB::table('park')
|
|
->orderBy('park_id', 'asc')
|
|
->pluck('park_name', 'park_id')
|
|
->toArray();
|
|
|
|
$userTypeOptions = DB::table('usertype')
|
|
->orderBy('user_categoryid')
|
|
->select([
|
|
'user_categoryid',
|
|
DB::raw("
|
|
COALESCE(
|
|
NULLIF(TRIM(CONCAT_WS('/',
|
|
NULLIF(TRIM(usertype_subject1), ''),
|
|
NULLIF(TRIM(usertype_subject2), ''),
|
|
NULLIF(TRIM(usertype_subject3), '')
|
|
)), ''),
|
|
print_name
|
|
) AS label
|
|
")
|
|
])
|
|
->pluck('label', 'user_categoryid')
|
|
->toArray();
|
|
|
|
$priceOptions = Schema::hasTable('price')
|
|
? DB::table('price')
|
|
->orderBy('price_parkplaceid')
|
|
->pluck('prine_name', 'price_parkplaceid')
|
|
->toArray()
|
|
: [];
|
|
|
|
$psectionOptions = Schema::hasTable('psection')
|
|
? DB::table('psection')
|
|
->orderBy('psection_id')
|
|
->pluck(
|
|
Schema::hasColumn('psection', 'psection_subject') ? 'psection_subject' : 'psection_id',
|
|
'psection_id'
|
|
)
|
|
->toArray()
|
|
: [];
|
|
$ptypeOptions = Schema::hasTable('ptype')
|
|
? DB::table('ptype')
|
|
->orderBy('ptype_id')
|
|
->pluck(
|
|
Schema::hasColumn('ptype', 'ptype_subject') ? 'ptype_subject' : 'ptype_id',
|
|
'ptype_id'
|
|
)
|
|
->toArray()
|
|
: [];
|
|
|
|
return [
|
|
'row' => $row,
|
|
'userOptions' => $userOptions,
|
|
'parkOptions' => $parkOptions,
|
|
'userTypeOptions' => $userTypeOptions,
|
|
'priceOptions' => $priceOptions,
|
|
'psectionOptions' => $psectionOptions,
|
|
'ptypeOptions' => $ptypeOptions,
|
|
|
|
];
|
|
}
|
|
|
|
/**
|
|
* 更新
|
|
*/
|
|
public function update(int $reserveId, array $payload): void
|
|
{
|
|
$exists = DB::table('reserve')->where('reserve_id', $reserveId)->exists();
|
|
if (!$exists) {
|
|
abort(404);
|
|
}
|
|
|
|
$data = [
|
|
'user_categoryid' => $payload['userCategoryId'],
|
|
'contract_id' => $payload['contractId'],
|
|
'contract_created_at' => $payload['contractCreatedAt'],
|
|
|
|
'user_id' => $payload['userId'],
|
|
'park_id' => $payload['parkId'],
|
|
'price_parkplaceid' => $payload['priceParkplaceId'],
|
|
'psection_id' => $payload['psectionId'],
|
|
'ptype_id' => $payload['ptypeId'],
|
|
'reserve_date' => $payload['reserveDate'],
|
|
'reserve_start' => $payload['reserveStart'],
|
|
'reserve_end' => $payload['reserveEnd'],
|
|
'valid_flag' => $payload['validFlag'],
|
|
'reserve_reduction' => $payload['reserveReduction'],
|
|
'reserve_auto_remind' => $payload['reserveAutoRemind'],
|
|
'reserve_manual_remind' => $payload['reserveManualRemind'],
|
|
'800m_flag' => $payload['flag800m'],
|
|
'reserve_cancelday' => $payload['reserveCancelday'],
|
|
'reserve_manual' => $payload['reserveManual'],
|
|
'reserve_notice' => $payload['reserveNotice'],
|
|
'reserve_type' => $payload['reserveType'],
|
|
'sent_date' => $payload['sentDate'],
|
|
'reserve_order' => $payload['reserveOrder'],
|
|
|
|
'ope_id' => $payload['opeId'],
|
|
'updated_at' => now(),
|
|
];
|
|
|
|
DB::table('reserve')
|
|
->where('reserve_id', $reserveId)
|
|
->update($data);
|
|
}
|
|
|
|
/**
|
|
* 複数削除
|
|
*
|
|
* @return int 削除件数
|
|
*/
|
|
public function deleteMany(array $ids): int
|
|
{
|
|
if ($ids === []) {
|
|
return 0;
|
|
}
|
|
|
|
return (int) DB::table('reserve')
|
|
->whereIn('reserve_id', $ids)
|
|
->delete();
|
|
}
|
|
|
|
private function baseQuery(): Builder
|
|
{
|
|
return DB::table('reserve as r')
|
|
->leftJoin('user as u', 'u.user_id', '=', 'r.user_id')
|
|
->leftJoin('park as p', 'p.park_id', '=', 'r.park_id')
|
|
->leftJoin('psection as ps', 'ps.psection_id', '=', 'r.psection_id')
|
|
->leftJoin('ptype as pt', 'pt.ptype_id', '=', 'r.ptype_id')
|
|
->leftJoin('usertype as t', 't.user_categoryid', '=', 'r.user_categoryid')
|
|
->select([
|
|
'r.reserve_id',
|
|
'r.contract_id',
|
|
'r.contract_created_at',
|
|
'r.user_categoryid',
|
|
'r.user_id',
|
|
'r.park_id',
|
|
'r.price_parkplaceid',
|
|
'r.psection_id',
|
|
'r.ptype_id',
|
|
'r.reserve_date',
|
|
|
|
'r.reserve_reduction',
|
|
'r.reserve_auto_remind',
|
|
'r.reserve_manual_remind',
|
|
DB::raw('r.`800m_flag` as flag_800m'),
|
|
'r.reserve_cancelday',
|
|
'r.valid_flag',
|
|
'r.ope_id',
|
|
'r.reserve_manual',
|
|
'r.reserve_notice',
|
|
'r.sent_date',
|
|
'r.reserve_order',
|
|
'r.reserve_type',
|
|
|
|
DB::raw('u.user_name as user_name'),
|
|
DB::raw('p.park_name as park_name'),
|
|
DB::raw('ps.psection_subject as psection_subject'),
|
|
DB::raw('pt.ptype_subject as ptype_subject'),
|
|
|
|
DB::raw('t.usertype_subject1 as usertype_subject1'),
|
|
DB::raw('t.usertype_subject2 as usertype_subject2'),
|
|
DB::raw('t.usertype_subject3 as usertype_subject3'),
|
|
]);
|
|
}
|
|
|
|
|
|
|
|
private function applyFilters(Builder $q, array $payload): void
|
|
{
|
|
if ($payload['userId'] !== '') {
|
|
$q->where('r.user_id', 'like', '%' . $payload['userId'] . '%');
|
|
}
|
|
|
|
if ($payload['parkId'] !== null) {
|
|
$q->where('r.park_id', '=', $payload['parkId']);
|
|
}
|
|
|
|
if (!empty($payload['fromDt'])) {
|
|
$q->whereDate('r.reserve_date', '>=', $payload['fromDt']);
|
|
}
|
|
|
|
if (!empty($payload['toDt'])) {
|
|
$q->whereDate('r.reserve_date', '<=', $payload['toDt']);
|
|
}
|
|
|
|
if ($payload['keyword'] !== '') {
|
|
$keyword = $payload['keyword'];
|
|
|
|
$q->where(function (Builder $w) use ($keyword): void {
|
|
$w->where('u.user_name', 'like', '%' . $keyword . '%')
|
|
->orWhere('u.user_phonetic', 'like', '%' . $keyword . '%');
|
|
});
|
|
}
|
|
|
|
if (in_array($payload['validFlag'], ['0', '1'], true)) {
|
|
$q->where('r.valid_flag', '=', (int) $payload['validFlag']);
|
|
}
|
|
}
|
|
|
|
private function applySort(Builder $q, string $sort, string $sortType): void
|
|
{
|
|
$sortMap = [
|
|
'reserve_id' => DB::raw('r.reserve_id'),
|
|
'contract_id' => DB::raw('r.contract_id'),
|
|
'contract_created_at' => DB::raw('r.contract_created_at'), //
|
|
'user_categoryid' => DB::raw('r.user_categoryid'),
|
|
'user_id' => DB::raw('r.user_id'),
|
|
'reserve_date' => DB::raw('r.reserve_date'),
|
|
|
|
'park_name' => DB::raw('p.park_name'),
|
|
'price_parkplaceid' => DB::raw('r.price_parkplaceid'),
|
|
|
|
'psection_subject' => DB::raw('ps.psection_subject'),
|
|
'ptype_subject' => DB::raw('pt.ptype_subject'),
|
|
|
|
'reserve_reduction' => DB::raw('r.reserve_reduction'),
|
|
'reserve_auto_remind' => DB::raw('r.reserve_auto_remind'),
|
|
'reserve_manual_remind' => DB::raw('r.reserve_manual_remind'),
|
|
|
|
'flag_800m' => DB::raw('r.`800m_flag`'), //
|
|
'reserve_cancelday' => DB::raw('r.reserve_cancelday'),
|
|
'valid_flag' => DB::raw('r.valid_flag'),
|
|
|
|
'sent_date' => DB::raw('r.sent_date'),
|
|
'reserve_manual' => DB::raw('r.reserve_manual'),
|
|
'reserve_notice' => DB::raw('r.reserve_notice'),
|
|
'reserve_order' => DB::raw('r.reserve_order'),
|
|
|
|
'reserve_type' => DB::raw('r.reserve_type'), //
|
|
];
|
|
|
|
if (!isset($sortMap[$sort])) {
|
|
return;
|
|
}
|
|
|
|
$q->orderBy($sortMap[$sort], $sortType);
|
|
}
|
|
|
|
|
|
private function sortableKeys(): array
|
|
{
|
|
return [
|
|
'reserve_id',
|
|
'contract_id',
|
|
'contract_created_at',
|
|
'user_categoryid',
|
|
'user_id',
|
|
'reserve_date',
|
|
|
|
'park_name',
|
|
'price_parkplaceid',
|
|
|
|
'psection_subject',
|
|
'ptype_subject',
|
|
|
|
'reserve_reduction',
|
|
'reserve_auto_remind',
|
|
'reserve_manual_remind',
|
|
|
|
'flag_800m',
|
|
'reserve_cancelday',
|
|
'valid_flag',
|
|
|
|
'sent_date',
|
|
'reserve_manual',
|
|
'reserve_notice',
|
|
'reserve_order',
|
|
|
|
'reserve_type',
|
|
];
|
|
}
|
|
|
|
|
|
private function normalizeSort(string $sort, array $sortable): string
|
|
{
|
|
return in_array($sort, $sortable, true) ? $sort : 'reserve_id';
|
|
}
|
|
|
|
private function normalizeSortType(string $sortType): string
|
|
{
|
|
$sortType = strtolower($sortType);
|
|
return $sortType === 'desc' ? 'desc' : 'asc';
|
|
}
|
|
|
|
private function attachPriceNames(LengthAwarePaginator $list): void
|
|
{
|
|
$placeIds = $list->getCollection()
|
|
->pluck('price_parkplaceid')
|
|
->filter()
|
|
->unique()
|
|
->values()
|
|
->all();
|
|
|
|
if ($placeIds === []) {
|
|
return;
|
|
}
|
|
|
|
$priceNames = Price::query()
|
|
->whereIn('price_parkplaceid', $placeIds)
|
|
->pluck('prine_name', 'price_parkplaceid')
|
|
->toArray();
|
|
|
|
$list->setCollection(
|
|
$list->getCollection()->map(function (object $row) use ($priceNames): object {
|
|
$id = $row->price_parkplaceid ?? null;
|
|
|
|
$row->display_prine_name = ($id !== null && array_key_exists($id, $priceNames))
|
|
? $priceNames[$id]
|
|
: null;
|
|
|
|
return $row;
|
|
})
|
|
);
|
|
}
|
|
|
|
|
|
private function getUnifiedPriceOptions()
|
|
{
|
|
return Price::query()
|
|
->select('price_parkplaceid', 'prine_name')
|
|
->get()
|
|
->sortBy('price_parkplaceid', SORT_NATURAL)
|
|
->unique('price_parkplaceid')
|
|
->values();
|
|
}
|
|
|
|
}
|