admin_api/app/Services/RecordPlatformData.php

<?php


namespace App\Services;


use App\Facade\TableName;
use Carbon\Carbon;
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Facades\DB;

class RecordPlatformData
{
    // 注册留存 $date = Y-m-d
    public function Retain($time1, $time2, $field)
    {
        //$time1 = Carbon::parse($time1)->format('Y-m-d');
        $time2 = $time2->format('Ymd');

        $RecordPlatformDataModel = new \App\Models\RecordPlatformData();
        $count = DB::connection('read')
            ->table($this->withNoLock(TableName::QPAccountsDB() . 'AccountsInfo as ai'))
            ->whereExists(function ($query) use ($time2) {
                $query->from($this->withNoLock(TableName::QPRecordDB() . 'RecordUserGamePlay as rl'))
                    ->select('rl.UserID')
//                    ->whereRaw("rl.UserID=ai.UserID and DateID=CONVERT(VARCHAR(100),DATEADD(dd,$dateNum,'$date'),112)");
                    ->whereRaw("rl.UserID=ai.UserID and DateID=$time2");
            })
            ->whereDate('RegisterDate', Carbon::parse($time1)->format('Y-m-d'))
            ->selectRaw("count(ai.UserID) $field,Channel")
            ->groupBy('Channel')
            ->get();

        $GroupChannelList = $count->map(function ($value) {
            return (array)$value;
        })->toArray();

        $AllChannelList = $count->sum($field);

        foreach ($GroupChannelList as $GroupChannel) {

            $firstChannel = $RecordPlatformDataModel
                ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $time1])
                ->lock('with(nolock)')
                ->first();
            if ($firstChannel) {
                $RecordPlatformDataModel
                    ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $time1])
                    ->update([$field => $GroupChannel[$field]]);
            } else {
                $RecordPlatformDataModel->insert([
                    'Channel' => $GroupChannel['Channel'],
                    'DateID' => $time1,
                    $field => $GroupChannel[$field],
                ]);
            }
        }

        $firstAllChannel = $RecordPlatformDataModel
            ->where(['Channel' => -1, 'DateID' => $time1])
            ->lock('with(nolock)')
            ->first();
        if ($firstAllChannel) {
            $RecordPlatformDataModel
                ->where(['Channel' => -1, 'DateID' => $time1])
                ->update([$field => $AllChannelList]);
        } else {
            $RecordPlatformDataModel->insert([
                'Channel' => -1,
                'DateID' => $time1,
                $field => $AllChannelList,
            ]);
        }

        return true;

    }

    // 活跃留存 $date = Y-m-d
    public function activeRetain($time1, $time2, $field)
    {
        $time2 = $time2->format('Ymd');
        $RecordPlatformDataModel = new \App\Models\RecordPlatformData();


        $count = DB::connection('read')
            ->table($this->withNoLock(TableName::QPRecordDB() . 'RecordUserLogin as rul'))
            ->join($this->withNoLock(TableName::QPAccountsDB() . 'AccountsInfo as ai'), 'rul.UserID', 'ai.UserID')
            ->whereExists(function ($query) use ($time2) {
                $query->from($this->withNoLock(TableName::QPRecordDB() . 'RecordUserLogin as rl'))
                    ->select('rl.UserID')
                    ->whereRaw("rl.UserID=rul.UserID and DateID=$time2");
            })
            ->where('DateID', $time1)
            ->selectRaw("count(rul.UserID) $field,Channel")
            ->groupBy('Channel')
            ->get();

        $GroupChannelList = $count->map(function ($value) {
            return (array)$value;
        })->toArray();
        $AllChannelList = $count->sum($field);

        foreach ($GroupChannelList as $GroupChannel) {

            $firstChannel = $RecordPlatformDataModel
                ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $time1])
                ->lock('with(nolock)')
                ->first();
            if ($firstChannel) {
                $RecordPlatformDataModel
                    ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $time1])
                    ->update([$field => $GroupChannel[$field]]);
            } else {
                $RecordPlatformDataModel->insert([
                    'Channel' => $GroupChannel['Channel'],
                    'DateID' => $time1,
                    $field => $GroupChannel[$field],
                ]);
            }
        }

        $firstAllChannel = $RecordPlatformDataModel
            ->where(['Channel' => -1, 'DateID' => $time1])
            ->lock('with(nolock)')
            ->first();
        if ($firstAllChannel) {
            $RecordPlatformDataModel
                ->where(['Channel' => -1, 'DateID' => $time1])
                ->update([$field => $AllChannelList]);
        } else {
            $RecordPlatformDataModel->insert([
                'Channel' => -1,
                'DateID' => $time1,
                $field => $AllChannelList,
            ]);
        }

        return true;

    }

    // 付费留存 $date = Y-m-d

    /**
     * @param $time1   // 充值日期
     * @param $time2   // 充值第二天日期
     * @param $field   // 查找/修改 的字段
     * @return bool
     */
    public function payRetain($time1, $time2, $field)
    {
        $time2 = $time2->format('Ymd');
        $RecordPlatformDataModel = new \App\Models\RecordPlatformData();
        $dateID = $time1;

        $count = DB::connection('read')
            ->table($this->withNoLock(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew as record'))
            ->join($this->withNoLock(TableName::QPAccountsDB() . 'AccountsInfo as ai'), 'record.UserID', 'ai.UserID')
            ->whereExists(function ($query) use ($time2) {
                $query->from($this->withNoLock(TableName::QPRecordDB() . 'RecordUserGamePlay as rl'))
                    ->select('rl.UserID')
                    ->whereRaw("rl.UserID=record.UserID and DateID=$time2");
            })
            ->where('Recharge', '>', 0)
            ->where('DateID', $time1)
            ->where([
                ['ai.RegisterDate', '>=', Carbon::createFromFormat('Ymd', $time1)->format('Y-m-d')],
                ['ai.RegisterDate', '<', Carbon::createFromFormat('Ymd', $time1)->addDay(1)
                    ->format('Y-m-d')]
            ])
            ->selectRaw("count(record.UserID) $field,Channel")
            ->groupBy('Channel')
            ->get();

        $GroupChannelList = $count->map(function ($value) {
            return (array)$value;
        })->toArray();
        $AllChannelList = $count->sum($field);

        foreach ($GroupChannelList as $GroupChannel) {

            $firstChannel = $RecordPlatformDataModel
                ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $dateID])
                ->lock('with(nolock)')
                ->first();
            if ($firstChannel) {
                $RecordPlatformDataModel
                    ->where(['Channel' => $GroupChannel['Channel'], 'DateID' => $dateID])
                    ->update([$field => $GroupChannel[$field]]);
            } else {
                $RecordPlatformDataModel->insert([
                    'Channel' => $GroupChannel['Channel'],
                    'DateID' => $dateID,
                    $field => $GroupChannel[$field],
                ]);
            }
        }

        $firstAllChannel = $RecordPlatformDataModel
            ->where(['Channel' => -1, 'DateID' => $dateID])
            ->lock('with(nolock)')
            ->first();
        if ($firstAllChannel) {
            $RecordPlatformDataModel
                ->where(['Channel' => -1, 'DateID' => $dateID])
                ->update([$field => $AllChannelList]);
        } else {
            $RecordPlatformDataModel->insert([
                'Channel' => -1,
                'DateID' => $dateID,
                $field => $AllChannelList,
            ]);
        }

        return true;

    }

    /**
     * 统计老付费用户（非当日注册）当日有/无充值的平均游戏局数，写入 RecordPlatformData
     *
     * @param string $dateID Ymd
     * @param \Carbon\Carbon $date
     * @return bool
     */
    public function oldPayUserAvgGameCountStat($dateID, $date)
    {
        $dao = new \App\dao\RecordPlatformData\RecordPlatformData();
        $stats = $dao->oldPayUserAvgGameCountStatBundle($dateID, $date);

        $withRecharge = $stats['withRecharge'];
        $withoutRecharge = $stats['withoutRecharge'];
        $withRechargeAll = $stats['withRechargeAll'];
        $withoutRechargeAll = $stats['withoutRechargeAll'];

        $withoutRechargeByChannel = $withoutRecharge->keyBy('Channel');

        foreach ($withRecharge as $item) {
            $paySecond = $withoutRechargeByChannel->get($item->Channel);
            $dao->Update([
                'DateID' => $dateID,
                'PayGameCount' => round($item->PayGameCount ?? 0, 2),
                'PaySecond' => round($paySecond->PaySecond ?? 0, 2),
            ], $item->Channel, $item->Channel, $dateID);
        }

        foreach ($withoutRecharge as $item) {
            if ($withRecharge->firstWhere('Channel', $item->Channel)) {
                continue;
            }
            $dao->Update([
                'DateID' => $dateID,
                'PayGameCount' => 0,
                'PaySecond' => round($item->PaySecond ?? 0, 2),
            ], $item->Channel, $item->Channel, $dateID);
        }

        $dao->Update([
            'DateID' => $dateID,
            'PayGameCount' => round($withRechargeAll->PayGameCount ?? 0, 2),
            'PaySecond' => round($withoutRechargeAll->PaySecond ?? 0, 2),
        ], -1, -1, $dateID);

        return true;
    }

    /**
     * Build SQL Server table expression with NOLOCK hint for read-heavy statistics queries.
     *
     * @param string $table
     * @return Expression
     */
    private function withNoLock($table)
    {
        return DB::raw($table . ' WITH (NOLOCK)');
    }

}