kwaiAD/app/Models/JxStarVideoF...

<?php

namespace App\Models;

use App\Support\Log;
use Illuminate\Database\Eloquent\Model;

class JxStarVideoFlowOrderHourData extends Model
{
    protected $table = 'jx_star_video_flow_order_hour_data';
    public $timestamps = false;
    protected static $unguarded = true;

    public static function getHourData($advertiserIdList, $startTime, $endTime) {
        if(empty($advertiserIdList)) return [];
        $query = self::query();

        if(!empty($advertiserIdList)) {
            $query->whereIn('advertiser_id', $advertiserIdList);
        }
        if(!empty($startTime)) {
            $query->where('date', '>=', date("Y-m-d H:00:00", strtotime($startTime)));
        }
        if(!empty($endTime)) {
            $query->where('date', '<=', date("Y-m-d H:00:00", strtotime($endTime)));
        }

        return $query->selectRaw('date,
				sum(consume_amount_incr) as consume_amount,
				sum(play_incr) as play,
				sum(action_incr) as action,
				sum(conversion_incr) as conversion,
				sum(submissions_number_incr) as submissions_number')
            ->where('enable', 1)
            ->groupBy('date')
            ->get()->keyBy('date')->toArray();
    }

    public static function getDataByHourList($advertiserIdList, $timeList) {
        if(empty($advertiserIdList)) return [];

        $query = self::query()->where('enable', 1);
        if(!empty($advertiserIdList)) {
            $query->whereIn('advertiser_id', $advertiserIdList);
        }
        if(is_array($timeList) && !empty($timeList)) {
            $query->whereIn('date', $timeList);
        }

        return $query->selectRaw('date,
				sum(consume_amount_incr) as consume_amount,
				sum(play_incr) as play,
				sum(action_incr) as action,
				sum(conversion_incr) as conversion,
				sum(submissions_number_incr) as submissions_number')
            ->where('enable', 1)
            ->groupBy('date')
            ->get()->keyBy('date')->toArray();
    }

    # 自动删除15天之前的统计数据
    public static function deleteRedundanceData() {
        $date = date('Y-m-d', strtotime('-14 days'));
        $minId = self::query()->where('date', $date)->min('id');
        self::query()->where('id', '<', $minId)->delete();
    }
}