admin_api/app/Http/logic/admin/UserLogicController.php

<?php


namespace App\Http\logic\admin;


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

class UserLogicController extends BaseLogicController
{
    protected $switch = 1;  // 控制变化原因开关  1开启 -1关闭

    protected $ids = [15, 39, 37, 33, 1, 46, 47, 48, 50, 53, 54, 21, 36, 42, 44, 45, 49, 51, 52, 58, 59, 70, 72];   // 总得变化原因
    //全部彩金-- 21:绑定手机赠送 36:推广赚金(彩金) 42:邮件附件(彩金) 44:签到奖励 45:充值金额(彩金) 49:月卡(彩金) 51:首充礼包(彩金) 52:邮件附件（群发彩金）70:反水活动彩金
    protected $LotteryMoney = [21, 33, 36, 37, 42, 44, 45, 49, 51, 52, 59, 70, 72];

    public function scoreChange($start_time, $end_time, $describe, $UserID, $ChangeScoreMin, $ChangeScoreMax, $ChangeScoreSort)
    {

        $where = [];

        !empty($ChangeScoreMax) && $ChangeScoreMax = (int)$ChangeScoreMax * NumConfig::NUM_VALUE;
        !empty($ChangeScoreMin) && $ChangeScoreMin = (int)$ChangeScoreMin * NumConfig::NUM_VALUE;

        if ((!empty($ChangeScoreMax) || $ChangeScoreMax === '0') && (!empty($ChangeScoreMin) || $ChangeScoreMin === '0')) {
            $where[] = [function ($obj) use ($ChangeScoreMin, $ChangeScoreMax) {
                $obj->whereBetween('ChangeScore', [$ChangeScoreMin, $ChangeScoreMax]);
            }];
        } elseif (!empty($ChangeScoreMin) || $ChangeScoreMin === '0') {
            $where[] = ['ChangeScore', '>=', $ChangeScoreMin];
        } elseif (!empty($ChangeScoreMax) || $ChangeScoreMax === '0') {
            $where[] = ['ChangeScore', '<=', $ChangeScoreMax];
        }

        !empty($UserID) && $where[] = ['ai.GameID', 'like', '%' . $UserID . '%'];

        if ($describe == -1) {
            $where[] = [function ($where) {

                $where->whereIn('sc.Reason', $this->LotteryMoney);
            }];
        } else {
            !empty($describe) && $where[] = ['sc.Reason', '=', $describe];
        }

        $Reason = [];
        if ($this->switch == 1) {
            $Reason = DB::connection('read')->table('QPRecordDB.dbo.YN_RecordScoreConfig')->whereIn('Reason', $this->ids)->get();
        }

        // 默认查询近一天的数据

        $start_time = $start_time ?: date("Y-m-d");
        $end_time = $end_time ?: date("Y-m-d");
        $where[] = ['si.UpdateTime', '>=', $start_time . ' 00:00:00'];
        $where[] = ['si.UpdateTime', '<=', $end_time . ' 23:59:59'];

        if (!empty($ChangeScoreSort)) {
            $sort = "si.ChangeScore $ChangeScoreSort";
        } else {
            $sort = 'UpdateTime desc';
        }

        ////////////////////////
        // 查询范围需要在同一个月份，暂时不支持跨月份查询
//        $from_mm = intval(date('Ym', strtotime($start_time)));
//        $to_mm = intval(date('Ym', strtotime($end_time)));
//        if ($from_mm != $to_mm) {
//            throw new Exception('暂不支持跨月查询，开始时间和结束时间请选择相同月份');
//        }

        //确定有效表范围，从202104月开始到当前月
//        if ($from_mm < 202104 || $from_mm > intval(date('Ym'))) {
//            throw new Exception('超出时间范围，开始时间应大于等于2021年4月，小于等于当前月');
//        }

        $table = 'QPRecordDB.dbo.RecordUserScoreChange';
        //////////////////////

        $list = DB::connection('read')->table($table . ' as si')
            ->join('QPRecordDB.dbo.YN_RecordScoreConfig as sc', 'sc.Reason', '=', 'si.Reason')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'si.UserID', '=', 'ai.UserID')
            //->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gi', 'gi.ServerID', 'si.ServerID')
            ->select('si.UserID', 'ai.GameID', 'si.BeforeScore', 'si.ChangeScore', 'si.AfterScore', 'si.UpdateTime', 'sc.Describe')
            ->where($where)
            ->orderByRaw($sort)
            ->paginate(10);
        foreach ($list as $key => &$value) {
            $value->BeforeScore = number_float($value->BeforeScore / 100);
            $value->ChangeScore = number_float($value->ChangeScore / 100);
            $value->AfterScore = number_float($value->AfterScore / 100);
            $value->ServerName = '';
        }

        !empty($ChangeScoreMax) && $ChangeScoreMax /= NumConfig::NUM_VALUE;
        !empty($ChangeScoreMin) && $ChangeScoreMin /= NumConfig::NUM_VALUE;

        return ['list' => $list,
            'describe' => $describe,
            'start_time' => $start_time,
            'end_time' => $end_time,
            'UserID' => $UserID,
            'Reason' => $Reason,
            'ChangeScoreMax' => $ChangeScoreMax,
            'ChangeScoreMin' => $ChangeScoreMin,
            'ChangeScoreSort' => $ChangeScoreSort
        ];
    }


    public function bind_list($start_time, $end_time, $UserID, $Higher1ID, $Higher2ID, $SpreaderID, $request)
    {
        $where = [];

        !empty($start_time) && $where[] = ['ai1.RegisterDate', '>=', $start_time];
        !empty($end_time) && $where[] = ['ai1.RegisterDate', '<=', $end_time];
        !empty($UserID) && $where[] = ['ai1.GameID', '=', $UserID];
        !empty($Higher1ID) && $where[] = ['Higher1ID', '=', $Higher1ID];
        !empty($Higher2ID) && $where[] = ['Higher2ID', '=', $Higher2ID];


        if (empty($Higher1ID) && empty($Higher2ID)) {
            $where[] = ['downCount1', '>', 0];
        }

        if (!empty($SpreaderID)) {
            $spreader_id = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->where('GameID', $SpreaderID)
                ->value('UserID');
            $where[] = ['Higher1ID', '=', $spreader_id];
        }


        $field = ['ua.UserID', 'Higher1ID', 'Higher2ID', 'downCount1', 'downCount2', 'ai1.GameID', 'ai2.GameID as SpreaderID', 'ai1.RegisterDate', 'ai1.LastLogonDate'];

        $list = DB::connection('read')->table('QPAccountsDB.dbo.UserAgent as ua')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai1', 'ua.UserID', 'ai1.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai2', 'ua.Higher1ID', 'ai2.UserID')
            ->where($where)
            ->select($field)
            ->when($request->input('sort'), function ($q) use ($request) {
                $q->orderBy(DB::raw($request->input('sort')), 'desc');
            })
            ->paginate(10);
        $userIDs = array_column($list->items(), 'UserID');
        if ($userIDs) {
            $ID1 = DB::table('QPAccountsDB.dbo.UserAgent')->whereIn('Higher1ID', $userIDs)
                ->selectRaw('Higher1ID as UserID, sum(TotalReward1) as TotalReward')
                ->groupBy('Higher1ID')
                ->pluck('TotalReward', 'UserID');
            $ID2 = DB::table('QPAccountsDB.dbo.UserAgent')->whereIn('Higher2ID', $userIDs)
                ->selectRaw('Higher2ID as UserID, sum(TotalReward2) as TotalReward')
                ->groupBy('Higher2ID')
                ->pluck('TotalReward', 'UserID');
            foreach ($list as $k => $v) {
                $list[$k]->Rewards = ($ID1[$v->UserID] ?? 0) + ($ID2[$v->UserID] ?? 0);
                $list[$k]->Rewards = round($list[$k]->Rewards/100, 2);
            }
        }
        return compact('list', 'start_time', 'end_time', 'UserID', 'SpreaderID', 'Higher1ID', 'Higher2ID');
    }
}