admin_api/app/Services/RecordPlatformData.php

<?php


namespace App\Services;


use App\Facade\TableName;
use Carbon\Carbon;
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(TableName::QPAccountsDB().'AccountsInfo as ai')
            ->whereExists(function ($query) use ($time2) {
                $query->from(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])->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])->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(TableName::QPRecordDB().'RecordUserLogin as rul')
            ->join(TableName::QPAccountsDB().'AccountsInfo as ai','rul.UserID','ai.UserID')
            ->whereExists(function ($query) use ( $time2) {
                $query->from(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])->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])->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(TableName::QPRecordDB().'RecordUserDataStatisticsNew as record')
            ->join(TableName::QPAccountsDB().'AccountsInfo as ai','record.UserID','ai.UserID')
            ->whereExists(function ($query) use ($time2) {
                $query->from(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])->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])->first();
        if ($firstAllChannel) {
            $RecordPlatformDataModel->where(['Channel' => -1, 'DateID' => $dateID])->update([$field=>$AllChannelList]);
        }else{
            $RecordPlatformDataModel->insert(['Channel' => -1, 'DateID' => $dateID,$field=>$AllChannelList]);
        }

        return true;

    }
}