kknongchang_2506/app/Module/Transfer/Models/TransferFeeDailyStats.php

<?php

namespace App\Module\Transfer\Models;

use UCore\ModelCore;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

/**
 * 手续费每日统计模型
 *
 * field start
 * @property  int  $id  主键ID
 * @property  string  $stat_date  统计日期
 * @property  int  $transfer_app_id  划转应用ID
 * @property  int  $last_processed_order_id  最后处理的订单ID
 * @property  int  $in_order_count  转入订单数量
 * @property  float  $in_total_amount  转入总金额
 * @property  float  $in_fee_amount  转入手续费总额
 * @property  float  $in_avg_fee_rate  转入平均手续费率
 * @property  int  $out_order_count  转出订单数量
 * @property  float  $out_total_amount  转出总金额
 * @property  float  $out_fee_amount  转出手续费总额
 * @property  float  $out_avg_fee_rate  转出平均手续费率
 * @property  int  $total_order_count  总订单数量
 * @property  float  $total_amount  总交易金额
 * @property  float  $total_fee_amount  总手续费金额
 * @property  float  $avg_fee_rate  平均手续费率
 * @property  \Carbon\Carbon  $created_at  创建时间
 * @property  \Carbon\Carbon  $updated_at  更新时间
 * field end
 *
 * relation start
 * @property  \App\Module\Transfer\Models\TransferApp  $transferApp  划转应用
 * relation end
 */
class TransferFeeDailyStats extends ModelCore
{
    /**
     * 表名
     */
    protected $table = 'transfer_fee_daily_stats';

    // attrlist start
    protected $fillable = [
        'id',
        'stat_date',
        'transfer_app_id',
        'last_processed_order_id',
        'in_order_count',
        'in_total_amount',
        'in_fee_amount',
        'in_avg_fee_rate',
        'out_order_count',
        'out_total_amount',
        'out_fee_amount',
        'out_avg_fee_rate',
        'total_order_count',
        'total_amount',
        'total_fee_amount',
        'avg_fee_rate',
    ];
    // attrlist end

    /**
     * 属性类型转换
     */
    protected $casts = [
        'id' => 'integer',
        'stat_date' => 'date',
        'transfer_app_id' => 'integer',
        'last_processed_order_id' => 'integer',
        'in_order_count' => 'integer',
        'in_total_amount' => 'decimal:10',
        'in_fee_amount' => 'decimal:10',
        'in_avg_fee_rate' => 'decimal:5',
        'out_order_count' => 'integer',
        'out_total_amount' => 'decimal:10',
        'out_fee_amount' => 'decimal:10',
        'out_avg_fee_rate' => 'decimal:5',
        'total_order_count' => 'integer',
        'total_amount' => 'decimal:10',
        'total_fee_amount' => 'decimal:10',
        'avg_fee_rate' => 'decimal:5',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
    ];

    /**
     * 隐藏字段
     */
    protected $hidden = [];

    /**
     * 关联划转应用
     */
    public function transferApp(): BelongsTo
    {
        return $this->belongsTo(TransferApp::class, 'transfer_app_id');
    }

    /**
     * 获取指定日期和应用的统计记录
     *
     * @param string $date 统计日期
     * @param int $appId 应用ID
     * @return static|null
     */
    public static function getByDateAndApp(string $date, int $appId): ?static
    {
        return static::where('stat_date', $date)
            ->where('transfer_app_id', $appId)
            ->first();
    }

    /**
     * 获取指定应用的最后处理订单ID
     *
     * @param int $appId 应用ID
     * @return int
     */
    public static function getLastProcessedOrderId(int $appId): int
    {
        $record = static::where('transfer_app_id', $appId)
            ->orderBy('stat_date', 'desc')
            ->first();

        return $record ? $record->last_processed_order_id : 0;
    }

    /**
     * 获取指定日期范围的统计数据
     *
     * @param string $startDate 开始日期
     * @param string $endDate 结束日期
     * @param int $appId 应用ID（0表示所有应用）
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public static function getByDateRange(string $startDate, string $endDate, int $appId = 0)
    {
        $query = static::with('transferApp')
            ->whereBetween('stat_date', [$startDate, $endDate]);

        if ($appId > 0) {
            $query->where('transfer_app_id', $appId);
        }

        return $query->orderBy('stat_date', 'desc')
            ->orderBy('total_fee_amount', 'desc')
            ->get();
    }

    /**
     * 获取月度统计汇总
     *
     * @param int $year 年份
     * @param int $month 月份
     * @param int $appId 应用ID（0表示所有应用）
     * @return array
     */
    public static function getMonthlyStats(int $year, int $month, int $appId = 0): array
    {
        $query = static::whereYear('stat_date', $year)
            ->whereMonth('stat_date', $month);

        if ($appId > 0) {
            $query->where('transfer_app_id', $appId);
        }

        $stats = $query->selectRaw('
            COUNT(*) as stat_days,
            SUM(total_order_count) as total_orders,
            SUM(total_amount) as total_amount,
            SUM(total_fee_amount) as total_fee,
            AVG(avg_fee_rate) as avg_fee_rate,
            SUM(in_order_count) as total_in_orders,
            SUM(in_fee_amount) as total_in_fee,
            SUM(out_order_count) as total_out_orders,
            SUM(out_fee_amount) as total_out_fee
        ')->first();

        return [
            'year' => $year,
            'month' => $month,
            'stat_days' => $stats->stat_days ?? 0,
            'total_orders' => $stats->total_orders ?? 0,
            'total_amount' => $stats->total_amount ?? '0.0000000000',
            'total_fee' => $stats->total_fee ?? '0.0000000000',
            'avg_fee_rate' => $stats->avg_fee_rate ?? '0.00000',
            'total_in_orders' => $stats->total_in_orders ?? 0,
            'total_in_fee' => $stats->total_in_fee ?? '0.0000000000',
            'total_out_orders' => $stats->total_out_orders ?? 0,
            'total_out_fee' => $stats->total_out_fee ?? '0.0000000000',
        ];
    }

    /**
     * 获取应用汇总统计
     *
     * @param int $appId 应用ID（0表示所有应用）
     * @return array
     */
    public static function getAppSummary(int $appId = 0): array
    {
        $query = static::with('transferApp');

        if ($appId > 0) {
            $query->where('transfer_app_id', $appId);
        }

        $stats = $query->selectRaw('
            transfer_app_id,
            COUNT(*) as stat_days,
            SUM(total_order_count) as total_orders,
            SUM(total_amount) as total_amount,
            SUM(total_fee_amount) as total_fee,
            AVG(avg_fee_rate) as avg_fee_rate,
            SUM(in_order_count) as total_in_orders,
            SUM(in_fee_amount) as total_in_fee,
            SUM(out_order_count) as total_out_orders,
            SUM(out_fee_amount) as total_out_fee,
            MIN(stat_date) as first_stat_date,
            MAX(stat_date) as last_stat_date,
            MAX(last_processed_order_id) as last_processed_order_id
        ')
        ->groupBy('transfer_app_id')
        ->orderBy('total_fee', 'desc')
        ->get();

        return $stats->toArray();
    }

    /**
     * 创建或更新统计记录
     *
     * @param array $data 统计数据
     * @return static
     */
    public static function createOrUpdate(array $data): static
    {
        return static::updateOrCreate(
            [
                'stat_date' => $data['stat_date'],
                'transfer_app_id' => $data['transfer_app_id'],
            ],
            $data
        );
    }

    /**
     * 格式化手续费金额显示
     *
     * @return string
     */
    public function getFormattedTotalFeeAttribute(): string
    {
        return number_format($this->total_fee_amount, 4);
    }

    /**
     * 格式化手续费率显示
     *
     * @return string
     */
    public function getFormattedAvgFeeRateAttribute(): string
    {
        return number_format($this->avg_fee_rate * 100, 2) . '%';
    }

    /**
     * 获取统计日期的中文格式
     *
     * @return string
     */
    public function getFormattedStatDateAttribute(): string
    {
        return $this->stat_date->format('Y年m月d日');
    }
}