admin_api/app/Services/LogDayStatisticalByDayAndChannel.php

<?php


namespace App\Services;

use App\Http\helper\NumConfig;
use Illuminate\Support\Facades\DB;

class LogDayStatisticalByDayAndChannel
{

    public function updateData($UserID, $type = 1)
    {
        $UserRegisterInfo = DB::connection('write')->table('QPAccountsDB.dbo.UserRegisterInfo')
            ->where('UserID', $UserID)
            ->select('RegisterTime')
            ->first();

        if ($UserRegisterInfo) {
            $RegisterTime = $UserRegisterInfo->RegisterTime;
        } else {
            return true;
        }


        if ($type == 1) {   // 充值

            $list = DB::connection('write')->table('agent.dbo.order as o')
                ->join('QPAccountsDB.dbo.UserRegisterInfo as ai', 'o.user_id', '=', 'ai.UserID')
                ->where('RegisterTime', $RegisterTime)
                ->where('pay_status', 1)
                ->selectRaw('count(distinct(ai.UserID)) count,count(o.id) as FuFeiBi,sum(amount) amount,Channel')
                ->groupBy('Channel')
                ->lock('with(nolock)')
                ->get();
            $channels = [];
            $item = [];
            foreach ($list as &$value) {

                $channels[] = $value->Channel;

                $value->amount = (int)($value->amount / NumConfig::NUM_VALUE);

                if (isset($item[$RegisterTime])) {
                    $item[$RegisterTime]['NewPayCount'] += $value->count;
                    $item[$RegisterTime]['NewPayBi'] += $value->FuFeiBi;
                    $item[$RegisterTime]['NewPaySum'] += $value->amount;
                } else {
                    $item[$RegisterTime]['NewPayCount'] = $value->count;
                    $item[$RegisterTime]['NewPayBi'] = $value->FuFeiBi;
                    $item[$RegisterTime]['NewPaySum'] = $value->amount;
                }

                // 修改渠道
                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
                    ->whereDate('RecordDate', DB::raw("cast('{$RegisterTime}' as date)"))
                    ->where('Channel', $value->Channel)
                    ->update(['NewPayCount' => $value->count, 'NewPaySum' => $value->amount, 'NewPayBi' => $value->FuFeiBi]);
            }

            // 归因问题导致的渠道不准的数据  清0
            if (count($channels) > 0) {
                $channels[] = -1;
                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
                    ->whereNotIn('Channel', $channels)
                    ->whereDate('RecordDate', $RegisterTime)
                    ->update(['NewPayCount' => 0, 'NewPaySum' => 0, 'NewPayBi' => 0]);
            }

            // 修改总得
//            foreach ($item as $key => $value) {
//
//                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
//                    ->whereDate('RecordDate', DB::raw("cast('{$RegisterTime}' as date)"))
//                    ->where('Channel', -1)
//                    ->update(['NewPayCount' => $value['NewPayCount'], 'NewPaySum' => $value['NewPaySum'], 'NewPayBi' => $value['NewPayBi']]);
//            }

        } else {    // 提现

            $list = DB::connection('write')->table('QPAccountsDB.dbo.OrderWithDraw as o')
                ->join('QPAccountsDB.dbo.UserRegisterInfo as ai', 'o.UserID', 'ai.UserID')
                ->where('RegisterTime', $RegisterTime)
                ->where('State', 2)
                ->selectRaw('count(distinct(ai.UserID)) count,sum(WithDraw) WithDraw,count(o.RecordID) TiXianBi,Channel,sum(ServiceFee) ServiceFee')
                ->groupBy('Channel')
                ->get();
            $channels = [];
            $item = [];
            foreach ($list as &$value) {
                $channels[] = $value->Channel;
                if (isset($item[$RegisterTime])) {
                    $item[$RegisterTime]['TiXianCount'] += $value->count;
                    $item[$RegisterTime]['TiXianBi'] += $value->TiXianBi;
                    $item[$RegisterTime]['TiXianOkSum'] += $value->WithDraw;
                    $item[$RegisterTime]['TiXianShouXu'] += $value->ServiceFee;
                } else {
                    $item[$RegisterTime]['TiXianCount'] = $value->count;
                    $item[$RegisterTime]['TiXianBi'] = $value->TiXianBi;
                    $item[$RegisterTime]['TiXianOkSum'] = $value->WithDraw;
                    $item[$RegisterTime]['TiXianShouXu'] = $value->ServiceFee;
                }
                $TiXianOkSum = intval(round($value->WithDraw / 100));
                $TiXianShouXu = intval(round($value->ServiceFee / 100));
                // 修改渠道
                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
                    ->whereDate('RecordDate', DB::raw("cast('{$RegisterTime}' as date)"))
                    ->where('Channel', $value->Channel)
                    ->update(['TiXianCount' => $value->count, 'TiXianBi' => $value->TiXianBi, 'TiXianOkSum' => $TiXianOkSum, 'TiXianShouXu' => $TiXianShouXu]);
            }

            // 归因问题导致的渠道不准的数据  清0
            if (count($channels) > 0) {
                $channels[] = -1;
                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
                    ->whereNotIn('Channel', $channels)
                    ->whereDate('RecordDate', $RegisterTime)
                    ->update(['TiXianCount' => 0, 'TiXianBi' => 0, 'TiXianOkSum' => 0, 'TiXianShouXu' => 0]);
            }

            // 修改总得
//            foreach ($item as $key => $value) {
//                $TiXianOkSum = intval(round($value['TiXianOkSum'] / 100));
//                $TiXianShouXu = intval(round($value['TiXianShouXu'] / 100));
//                DB::connection('write')->table('QPRecordDB.dbo.logDayStatisticalByDayAndChannel')
//                    ->whereDate('RecordDate', DB::raw("cast('{$RegisterTime}' as date)"))
//                    ->where('Channel', -1)
//                    ->update(['TiXianCount' => $value['TiXianCount'], 'TiXianOkSum' => $TiXianOkSum, 'TiXianBi' => $value['TiXianBi'], 'TiXianShouXu' => $TiXianShouXu]);
//            }
        }
        return true;
    }
}