movie/app/Models/LoginLog.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use App\Models\DTO\SearchData;

class LoginLog extends Model
{
    protected $table = 'tb_login_log';
    protected $primaryKey = 'id';

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

    const CREATED_AT = 'created_at';
    const UPDATED_AT = null;
    const DELETED_AT = null;

    protected $guarded = [];

    /**
     * 로그인 이력 조회
     */
    public function data(SearchData $params): object
    {
        $query = $this->query();
        $query->select('tb_login_log.*', 'users.sid');

        if($params->keyword) {
            switch ($params->field) {
                case 'users.name' :
                case 'users.nickname' :
                case 'users.email' :
                case 'tb_login_log.ip_address' :
                case 'tb_login_log.created_at' :
                    $query->where($params->field, 'LIKE', "%{$params->keyword}%");
                    break;
                case 'users.id' :
                case 'users.sid' :
                    $query->where($params->field, '=', $params->keyword);
                    break;
            }
        }

        $query->leftJoin('users', 'users.id', '=', 'tb_login_log.user_id');
        $query->orderByDesc('tb_login_log.id');

        $list = $query->paginate($params->perPage, ['*'], 'page', $params->page);
        $total = $this->count();
        $rows = $list->count();

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

    /**
     * 로그인 통계
     */
    public function getLoginSuccessCount(string $dateType, string $startDate, string $endDate, int $success): object
    {
        $left = ($dateType === 'y') ? 4 : ($dateType === 'm' ? 7 : 10);
        $sql = sprintf("SELECT COUNT(*) AS cnt, LEFT(created_at, %s) AS day FROM tb_login_log
                                 WHERE success = %d AND LEFT(created_at, 10) >= '%s' AND LEFT(created_at, 10) <= '%s'
                                 GROUP BY created_at", $left, $success, $startDate, $endDate);

        $list = DB::select($sql);
        $total = count($list);

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

    /**
     * 로그인 성공 기록
     */
    public function setSucceedLog(string $email, string $reason): bool
    {
        return $this->insert([
            'user_id' => UID,
            'success' => 1,
            'account' => $email,
            'ip_address' => IP_ADDRESS,
            'reason' => $reason,
            'user_agent' => USER_AGENT,
            'url' => FULL_URL,
            'referer' => REFERER
        ]);
    }

    /**
     * 로그인 실패 기록
     */
    public function setFailedLog(string $email, string $reason): bool
    {
        return $this->insert([
            'user_id' => null,
            'success' => 0,
            'account' => $email,
            'reason' => $reason,
            'ip_address' => IP_ADDRESS,
            'user_agent' => USER_AGENT,
            'url' => FULL_URL,
            'referer' => REFERER
        ]);
    }

    /**
     * 로그인 기록 조회
     */
    public function logs(int $userID, SearchData $params): object
    {
        $query = $this->query();
        $query->where('user_id', $userID);

        if ($params->startDate || $params->endDate) {
            if ($params->startDate) {
                $query->where('created_at', '>=', $params->startDate . ' 00:00:00');
            }
            if ($params->endDate) {
                $query->where('created_at', '<=', $params->endDate . ' 23:59:59');
            }
        }

        $query->orderByDesc('id');
        $list = $query->paginate($params->perPage, ['*'], 'page', $params->page);
        $total = $list->total();
        $rows = $list->count();

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