movie/app/Models/BoardCategory.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use App\Http\Traits\PagingTrait;
use Kalnoy\Nestedset\NodeTrait;

class BoardCategory extends Model
{
    use PagingTrait;
    use NodeTrait;

    protected $table = 'tb_board_category';
    protected $primaryKey = 'id';

    public $keyType = 'int';
    public $incrementing = true;
    public $timestamps = true;

    const CREATED_AT = 'created_at';
    const UPDATED_AT = 'updated_at';

    protected $guarded = [];

    public function getParentIdName(): string
    {
        return 'parent_id';
    }

    public function getLftName(): string
    {
        return 'lft';
    }

    public function getRgtName(): string
    {
        return 'rgt';
    }

    /**
     * 게시판 분류 전체 조회
     */
    public function data(int $boardID, int $page, int $perPage): object
    {
        $sql = "SELECT COUNT(*) AS total FROM tb_board_category WHERE board_id = ?";
        $total = DB::selectOne($sql, [$boardID])->total;

        /*
        $sql = "SELECT
                    CASE WHEN LEVEL > 1 THEN CONCAT(REPEAT('    ', level - 1), '┗> ') ELSE '' END AS tab,
                    CASE WHEN LEVEL-1 > 0 THEN CONCAT(CONCAT(REPEAT('┗>', level - 2), '┗> '), bca.name)
                    ELSE bca.name
                    END AS title
                         , bca.*
                         , fnc.level
                FROM
                   (
                        SELECT fn_board_category() AS brd_category_key, @level AS level
                            FROM (SELECT @startWith:= 0, @id:=@startWith, @level:=0) vars
                        JOIN tb_board_category
                        WHERE
                        @id IS NOT NULL
                    ) fnc
                JOIN tb_board_category bca ON fnc.brd_category_key = bca.brd_category_key
                AND board_id = ?
                    LIMIT ?, ?";
        */
        $sql = 'CALL SP_BOARD_CATEGORY(?, ?, ?);';
        $params = [
            $boardID,
            (($page - 1) * $perPage),
            $perPage
        ];

        $list = $this->getPaginator(DB::select($sql, $params), $total, $perPage, $page);
        $row = $list->count();

        return (object)[
            'total' => $total,
            'rows' => $row,
            'list' => $list
        ];
    }

    /**
     * 분류 목록 조회
     */
    public function getCategoryList(int $boardID): object
    {
        $total = DB::selectOne("SELECT COUNT(*) AS total FROM tb_board_category WHERE board_id = ?", [
            $boardID
        ])->total;

        $list = $this->where('board_id', $boardID)->withDepth()->defaultOrder()->get()->toTree();
        $rows = $list->count();

        return (object)[
            'total' => $total,
            'rows' => $rows,
            'list' => $list
        ];
    }

    /**
     * 분류 조회
     */
    public function findCategory(int $categoryID): ?BoardCategory
    {
        return $this->newQuery()->find($categoryID);
    }

    /**
     * nested 분류 생성
     */
    public function register(array $params): bool
    {
        $parentID = $params['parent_id'];
        if (!$parentID) { // 부모 등록
            return (new BoardCategory($params))->save();
        } else { // 자식 등록
            return $this->findCategory($parentID)->appendNode(new BoardCategory($params));
        }
    }

    /**
     * 분류 수정
     */
    public function updater(array $params): bool
    {
        return $this->findCategory($params['id'])->update($params);
    }

    /**
     * 분류 삭제
     */
    public function remove(int $categoryID): int
    {
        return $this->destroy($categoryID);
    }

    /**
     * 분류 한 단계 위로
     */
    public function orderUp(int $categoryID): bool
    {
        return $this->findCategory($categoryID)->up();
    }

    /**
     * 분류 한 단계 아래로
     */
    public function orderDown(int $categoryID): bool
    {
        return $this->findCategory($categoryID)->down();
    }

    /**
     * 게시판 분류 조회
     */
    public function categories(int $boardID): object
    {
		$total = 0;
        $categories = $this->where('board_id', $boardID)->withDepth()->defaultOrder()->get()->toTree();
        if($categories->count() > 0) {
            foreach($categories as $i => $v) {
				$sql = '
					SELECT COUNT(*) AS `rows` FROM tb_post PST JOIN tb_board BRD ON BRD.id = PST.board_id
					WHERE 1
					AND BRD.id = ? AND BRD.is_display = 1 AND PST.board_category_id = ? AND PST.is_delete = 0;
				';
                $total += $v->rows = DB::selectOne($sql, [$v->board_id, $v->board_category_id])->rows;
                $categories[$i] = $v;
            }
        }
        return (object)[
			'total' => $total,
			'list' => $categories
		];
    }

    /**
     * 게시판 카테고리 PK 계산
     */
    public function nextKey(int $parentID, int $boardID): mixed
    {
        if ((string)$parentID === '0') {
            $result = $this->select('id')->where('board_id', $boardID)->get();
            $max = 0;
            foreach ($result as $key => $value) {
                $float = floatval($value->board_category_id);
                if ($float > $max) {
                    $max = $float;
                }
            }
            return intval($max + 1);
        }
        if (strpos($parentID, '.') === false) {
            $result = $this->select('id')->where('board_id', $boardID)->where('id', 'like', $parentID . '.%')->get();

            $max = 0;
            foreach ($result as $key => $value) {
                $float = $value->board_category_id;
                if ($float > $max) {
                    $max = $float;
                }
            }

            if ($max) {
                $keyExplode = explode('.', $max);
                $digit = substr($keyExplode[1], 0, 3) + 1;
                $ret = sprintf("%03d", $digit);
                return $keyExplode[0] . '.' . $ret;
            } else {
                return $parentID . '.001';
            }
        } else {
            $result = $this->select('id')->where('board_id', $boardID)->where('id', 'like', $parentID . '%')->get();

            $max = 0;
            foreach ($result as $key => $value) {
                $float = $value->board_category_id;
                if ($float > $max) {
                    $max = $float;
                }
            }
            if ((string)$max === (string)$parentID) {
                return $parentID . '001';
            } else {
                $keyExplode = explode('.', $max);
                $parent_id_explode = explode('.', $parentID);
                $parent_idLen = strlen($parent_id_explode[1]);
                $digit = substr($keyExplode[1], 0, $parent_idLen + 3) + 1;
                $res = sprintf("%0" . ($parent_idLen + 3) . "d", $digit);
                return $keyExplode[0] . '.' . $res;
            }
        }
    }
}