SATE-EasyReceipt/app/Repositories/UseSummaryRepository.php

<?php

namespace App\Repositories;

use App\Exceptions\AppCommonException;
use App\Models\ReceiptIssuingOrder;
use App\Models\SMSSendOrder;
use App\Repositories\BaseRepository;
use App\Util\SelectQueryUtil;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class UseSummaryRepository extends BaseRepository
{
    // 必須検索項目
    const CONDITION_CONTRACT_ID = 'contract_id';
    const CONDITION_ORDER_DATE_FROM = 'date_from';
    const CONDITION_ORDER_DATE_TO = 'date_to';

    protected const TABLE_TARGET = "target";
    protected const TABLE_SMS = "SMS";
    protected const TABLE_SUMMARY = "summary";

    protected Carbon $dateFrom;
    protected Carbon $dateTo;
    protected Carbon $dateToOrigin;

    /**
     * コレクションを取得する
     * 
     * @param array $condition
     * @return Collection<UseSummaryRepositoryData>
     */
    public function get(array $condition): Collection
    {
        return UseSummaryRepositoryData::makeList($this->builder($condition)->get());
    }

    protected function builder(array $condition): Builder
    {

        $contractId = data_get($condition, self::CONDITION_CONTRACT_ID);

        $dateFrom = data_get($condition, self::CONDITION_ORDER_DATE_FROM);
        $dateTo = data_get($condition, self::CONDITION_ORDER_DATE_TO);

        if (!($dateFrom instanceof Carbon) || !($dateTo instanceof Carbon)) {
            throw new AppCommonException('検索日付不正');
        }
        $this->dateFrom = $dateFrom->clone();
        $this->dateToOrigin = $dateTo->clone();
        $this->dateTo = $dateTo->clone()->addDay();

        $target = ReceiptIssuingOrder::getBuilder(self::TABLE_TARGET);

        if ($contractId) {
            $target->where(
                $this->makeColumnName([static::TABLE_TARGET, ReceiptIssuingOrder::COL_NAME_CONTRACT_ID]),
                $contractId
            );
        }

        $target->where(
            $this->makeColumnName([static::TABLE_TARGET, ReceiptIssuingOrder::COL_NAME_ORDER_DATETIME]),
            '>=',
            $dateFrom
        )
            ->where(
                $this->makeColumnName([static::TABLE_TARGET, ReceiptIssuingOrder::COL_NAME_ORDER_DATETIME]),
                '<',
                $dateTo->clone()->addDay()
            );


        $smsCount =  $target->clone()
            ->joinSub(
                SMSSendOrder::getBuilder(),
                self::TABLE_SMS,
                function (JoinClause $join) {
                    $join->on(
                        $this->makeColumnName([static::TABLE_SMS, SMSSendOrder::COL_NAME_RECEIPT_ISSUING_ORDER_ID]),
                        $this->makeColumnName([static::TABLE_TARGET, ReceiptIssuingOrder::COL_NAME_ID]),
                    );
                }
            )
            ->whereNotNull(SMSSendOrder::COL_NAME_SEND_DATETIME)
            ->groupBy(
                $this->makeColumnName([static::TABLE_TARGET, $this->groupByColumn()])
            )
            ->select([
                $this->makeColumnName([static::TABLE_TARGET, $this->groupByColumn()]),
                DB::raw('count(*) as sms_send_count'),
            ]);

        $orderCount = $target->clone()
            ->groupBy(
                $this->makeColumnName([static::TABLE_TARGET, $this->groupByColumn()])
            )
            ->select([
                $this->groupByColumn(),
                DB::raw(sprintf("count(*) as %s", 'receipt_order_count')),
                DB::raw(sprintf("count(%s) as %s", ReceiptIssuingOrder::COL_NAME_STATUS_ORDER_MAIL_DATETIME, 'mail_order_count')),
            ]);

        $summary = DB::table($orderCount, self::TABLE_SUMMARY)
            ->joinSub(
                $smsCount,
                self::TABLE_SMS,
                function (JoinClause $join) {
                    $join->on(
                        $this->makeColumnName([static::TABLE_SMS, $this->groupByColumn()]),
                        $this->makeColumnName([static::TABLE_SUMMARY, $this->groupByColumn()]),
                    );
                }
            );


        $main = $summary;

        $main->select($this->columns());
        // ソート
        $this->sort($main, $condition);
        $main->orderBy(
            $this->makeColumnName([static::TABLE_SUMMARY, $this->groupByColumn()]),
        );

        // リミット
        $this->limit($main, $condition);

        return $main;
    }

    protected function columns()
    {
        $sms = self::TABLE_SMS;
        $summary =  self::TABLE_SUMMARY;
        $columns =   [
            SelectQueryUtil::value($this->dateFrom->format('Y/m/d'), 'date_from'),
            SelectQueryUtil::value($this->dateToOrigin->format('Y/m/d'), 'date_to'),
            SelectQueryUtil::select([$summary, 'receipt_order_count'])->build(),
            SelectQueryUtil::select([$summary, 'mail_order_count'])->build(),
            SelectQueryUtil::select([$sms, 'sms_send_count'])->nullValue(0)->build(),
        ];
        return $columns;
    }

    protected function groupByColumn(): string
    {
        return ReceiptIssuingOrder::COL_NAME_SUMMARY_KEY1;
    }
}