<?php

namespace App\Models;

use App\Facade\TableName;
use App\Http\helper\NumConfig;
use App\Models\Account\AccountPhone;
use App\Models\Treasure\GameScoreInfo;
use App\Services\StoredProcedure;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Session;

class AccountsInfo extends Model
{
    const TABLE = 'QPAccountsDB.dbo.AccountsInfo';
    protected $table = self::TABLE;

    protected $primaryKey = 'UserID';
    public $timestamps = false;
    protected $fillable = ['UserID', 'SpreaderID', 'GameID', 'NickName', 'MemberOrder', 'MemberOverDate', 'Nullity', 'IsAndroid', 'Channel', 'RegisterDate','Registed'];

    public function gameScoreInfo()
    {
        return $this->hasOne(GameScoreInfo::class, 'UserID', 'UserID');
    }

    public function accountPhoneRelation()
    {
        return $this->hasOne(AccountPhone::class, 'UserID', 'UserID');
    }

    public function gameScoreLocker()
    {
        return $this->hasOne(GameScoreInfo::class, 'UserID', 'UserID');
    }


    //今日新增
    public static function today_register()
    {
        $today_register = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->whereDate('RegisterDate', date('Y-m-d'))
            ->selectRaw('count(*) as today_register,Channel')
            ->lock('with(nolock)')
            ->groupBy('Channel')
            ->pluck('today_register','Channel')->toArray();
//            ->first()->today_register;
        return $today_register;
    }

    public static function today_gameplay()
    {
        return DB::connection('sqlsrv')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->whereDate('RegisterDate', date('Y-m-d'))
            ->join(TableName::QPRecordDB() . 'RecordUserGameDayCount as rd', 'ai.UserID', 'rd.UserID')
            ->where('DateID', date('Ymd'))
            ->selectRaw('count(distinct(rd.UserID)) UserCount,Channel')
            ->groupBy('Channel')
            ->lock('with(nolock)')
            ->pluck('UserCount','Channel')->toArray();
    }
    public static function yesterday_gameplay()
    {
        return DB::connection('sqlsrv')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->whereDate('RegisterDate', date('Y-m-d', strtotime('-1 day')))
            ->join(TableName::QPRecordDB() . 'RecordUserGameDayCount as rd', 'ai.UserID', 'rd.UserID')
            ->where('DateID', date('Ymd', strtotime('-1 day')))
            ->selectRaw('count(distinct(rd.UserID)) UserCount,Channel')
            ->groupBy('Channel')
            ->lock('with(nolock)')
            ->pluck('UserCount','Channel')->toArray();
    }

    //昨日新增
    public static function yesterday_register()
    {

        $yesterday_register = Cache::remember('yesterday_register', Carbon::tomorrow(), function () {
            return $yesterday_register = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->selectRaw('count(*) as yesterday_register,Channel')
                ->whereRaw('RegisterDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
                ->whereRaw('RegisterDate<CONVERT(varchar(10),DATEADD(DAY,0,GETDATE()),120)')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->pluck('yesterday_register','Channel')->toArray();
//                ->first()->yesterday_register;
        });
        return $yesterday_register;
    }
    //昨日新增
    public static function yesterday_register_now()
    {
        $yesterday_register = Cache::remember('yesterday_register_now', 5, function () {
            return $yesterday_register = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->selectRaw('count(*) as yesterday_register,Channel')
                ->whereRaw('RegisterDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120) and RegisterDate<CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->pluck('yesterday_register','Channel')->toArray();
//                ->first()->yesterday_register;
        });
        return $yesterday_register;
    }

    //日活
    public static function today_live()
    {
        $today_live = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->selectRaw('count(*) as today_live,Channel')
            ->whereRaw('LastLogonDate>CONVERT(varchar(10),GETDATE(),120)')
            ->where('IsAndroid', 0)
            ->lock('with(nolock)')
            ->groupBy('Channel')
            ->pluck('today_live','Channel')->toArray();
//            ->first()->today_live;
        return $today_live;
    }

    //昨日日活
    public static function yesterday_live()
    {
        $yesterday_live = Cache::remember('yesterday_live', Carbon::tomorrow(), function () {
            return $yesterday_live = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as yesterday_live')
                // ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())=1')
                ->whereRaw('LastLogonDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
//                ->first()->yesterday_live;
        });
        return $yesterday_live;
    }

    //昨日日活
    public static function yesterday_live_now()
    {
        $yesterday_live = Cache::remember('yesterday_live_now', 5, function () {
            return $yesterday_live = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as yesterday_live')
                // ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())=1')
                ->whereRaw('LastLogonDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120) and LastLogonDate<CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
//                ->first()->yesterday_live;
        });
        return $yesterday_live;
    }

    //本周周活
    public static function week_login()
    {
        return $week_login = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//            ->selectRaw('count(*) as week_login')
            ->whereRaw('DATEDIFF(WEEK, LastLogonDate, GETDATE())=0')
            ->where('IsAndroid', 0)
            ->lock('with(nolock)')
            ->groupBy('Channel')
            ->selectRaw("count(*) as num,Channel")
            ->pluck('num','Channel')->toArray();
//            ->first()->week_login;
    }

    //上周周活
    public static function yweek_login()
    {
        $yweek_login = Cache::remember('yweek_login', Carbon::tomorrow(), function () {
            return $yweek_login = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as yweek_login')
                ->whereRaw('DATEDIFF(WEEK, LastLogonDate, GETDATE())=1')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
//                ->first()->yweek_login;
        });
        return $yweek_login;
    }

    //月活
    public static function month_login()
    {
        return $month_login = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//            ->selectRaw('count(*) as month_login')
            ->whereRaw('DATEDIFF(MONTH, LastLogonDate, GETDATE())=0')
            ->where('IsAndroid', 0)
            ->lock('with(nolock)')
            ->groupBy('Channel')
            ->selectRaw("count(*) as num,Channel")
            ->pluck('num','Channel')->toArray();
//            ->first()->month_login;
    }

    //上月月活
    public static function ymonth_login()
    {
        $ymonth_login = Cache::remember('ymonth_login', Carbon::tomorrow(), function () {
            return $ymonth_login = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as ymonth_login')
                ->whereRaw('DATEDIFF(MONTH, LastLogonDate, GETDATE())=1')
                ->where('IsAndroid', 0)
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
//                ->first()->ymonth_login;
        });
        return $ymonth_login;
    }

    //总注册用户
    public static function user_count()
    {
        return $user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->count('UserID');
//            ->lock('with(nolock)')
//            ->groupBy('Channel')
//            ->selectRaw("count(*) as num,Channel")
//            ->pluck('num','Channel')->toArray();
    }
    public static function user_count_byChannel()
    {
        return $user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
//            ->count('UserID');
            ->lock('with(nolock)')
            ->groupBy('Channel')
            ->selectRaw("count(*) as num,Channel")
            ->pluck('num','Channel')->toArray();
    }

    //休眠用户
    public static function dormancy_user()
    {
        $dormancy_user=Cache::remember('dormancy_user', Carbon::tomorrow(), function () {
            return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->selectRaw('count(*) as dormancy_user')
                ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>7')
                ->where('IsAndroid', 0)
                ->first()->dormancy_user;
//                ->lock('with(nolock)')
//                ->groupBy('Channel')
//                ->selectRaw("count(*) as num,Channel")
//                ->pluck('num','Channel')->toArray();
        });
        return $dormancy_user;
    }
    public static function dormancy_user_byChannel()
    {
        $dormancy_user=Cache::remember('dormancy_user_byChannel', Carbon::tomorrow(), function () {
            return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as dormancy_user')
                ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>7')
                ->where('IsAndroid', 0)
//                ->first()->dormancy_user;
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
        });
        return $dormancy_user;
    }

    //流失用户
    public static function ls_user()
    {


        $dormancy_user=Cache::remember('ls_user', Carbon::tomorrow(), function () {
            return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->selectRaw('count(*) as dormancy_user')
                ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>30')
                ->where('IsAndroid', 0)
                ->first()->dormancy_user;
//                ->lock('with(nolock)')
//                ->groupBy('Channel')
//                ->selectRaw("count(*) as num,Channel")
//                ->pluck('num','Channel')->toArray();
        });
        return $dormancy_user;
    }

    public static function ls_user_byChannel()
    {


        $dormancy_user=Cache::remember('ls_user_byChannel', Carbon::tomorrow(), function () {
            return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
//                ->selectRaw('count(*) as dormancy_user')
                ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>30')
                ->where('IsAndroid', 0)
//                ->first()->dormancy_user;
                ->lock('with(nolock)')
                ->groupBy('Channel')
                ->selectRaw("count(*) as num,Channel")
                ->pluck('num','Channel')->toArray();
        });
        return $dormancy_user;
    }


    // 可提现余额
    public function CashAble($UserID, $Score, $StatusValue)
    {
        $ReturnValue = StoredProcedure::GetWithDrawLimit($UserID)['ReturnValue'] ?? 0;
        return $ReturnValue / NumConfig::NUM_VALUE;
    }

    // 用户余额
    public function Score($UserID)
    {
        return DB::connection('write')->table('QPTreasureDB.dbo.GameScoreInfo')
            ->where('UserID', $UserID)
            ->value('Score');
    }

    // 用户标签
    public function labelType($type)
    {
        switch ($type) {
            case 1:
                $user_label = '普通会员';
                break;
            case 2:
                $user_label = '首充会员';
                break;
            case 3:
                $user_label = '复充会员';
                break;
            case 4:
                $user_label = '优质会员';
                break;
            case 5:
                $user_label = '尊贵会员';
                break;
            case 6:
                $user_label = '发育不良';
                break;
            case 7:
                $user_label = '异常观察';
                break;
            case 8:
                $user_label = '重点关注';
                break;
            case 20:
                $user_label = '其他1';
                break;
            case 21:
                $user_label = '其他2';
                break;
            default:
                $user_label = '普通会员';
                break;
        }
        return $user_label;
    }

    // 登录IP
    public function LoginIP($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPRecordDB() . 'RecordUserLogonStatistics as rs')
            ->join(TableName::QPRecordDB() . 'RecordUserLogonStatistics as rs1', 'rs.LogonIP', 'rs1.LogonIP')
            ->whereIn('rs.UserID', $UserIDs)
            ->selectRaw('count(distinct(rs1.UserID)) count,rs.UserID')
            ->groupBy('rs.UserID')
            ->pluck('count', 'UserID')->toArray();
    }

    // 用户备注
    public function remarks($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPAccountsDB() . 'IndividualDatum')
            ->whereIn('UserID', $UserIDs)
            ->select('UserID', 'UserNote')
            ->pluck('UserNote', 'UserID')->toArray();
    }

    // 邮件未领取金币
    public function mail($UserIDs)
    {
        $mail = DB::connection('read')->table(TableName::QPAccountsDB() . 'PrivateMail')
            ->whereIn('UserID', $UserIDs)
            ->where('MailStatus', '<', 3)
            ->select('UserID', 'BonusString')
            ->get();

        $mailData = [];
        foreach ($mail as $val) {
            $arr = explode(',', $val->BonusString);
            if (!empty($arr) && isset($arr[0]) && $arr[0] == 30000) {

                $value = $arr[1] ?? 0;

                if (isset($mailData[$val->UserID])) {
                    $mailData[$val->UserID] = $mailData[$val->UserID] + (int)$value;
                } else {
                    $mailData[$val->UserID] = $value;
                }
            }
        }
        return $mailData;
    }

    // 受控标签
    public function controlLabel($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPRecordDB() . 'RecordRechargeControl')
            ->whereIn('UserID', $UserIDs)
            ->where('State', '>', 0)
            ->pluck('State', 'UserID');
    }

    // 用户标签
    public function accountLabel($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountLabelDanControlLabel')
            ->whereIn('UserID', $UserIDs)
            ->select('type', 'IDColor', 'UserID')
            ->get();
    }

    // 用户手机号
    public function accountPhone($UserIDs)
    {

        return DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountPhone')
            ->whereIn('UserID', $UserIDs)
            ->pluck('PhoneNum', 'UserID')->toArray();

    }

    // 用户在线状态
    public function accountOnLine($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPTreasureDB() . 'GameScoreLocker as gsl')
            ->whereIn('UserID', $UserIDs)
            ->join(TableName::QPPlatformDB() . 'GameRoomInfo as gri', 'gsl.ServerID', 'gri.ServerID')
            ->whereRaw('datediff(hh,CollectDate,getdate())<=5')
            ->select('ServerName', 'UserID', 'gsl.KindID')
            ->get();
    }

    // 全部充值 -- 提现 -- 彩金 -- 总输赢 -- 总得提现手续费
    public function accountTotalStatistics($UserIDs)
    {
        return DB::connection('read')->table('QPRecordDB.dbo.RecordUserTotalStatistics')
            ->selectRaw('Recharge,Withdraw,Handsel,(WinScore + LostScore) Score,ServiceFee,UserID,Revenue,MaxDrawBase')
            ->whereIn('UserID', $UserIDs)
            ->get();
    }

    // 今日充值-- 今日提现-- 今日彩金 -- 总输赢
    public function accountTodayStatistics($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew')
            ->where('DateID', date('Ymd'))
            ->whereIn('UserID', $UserIDs)
            ->selectRaw('Withdraw,Handsel, Recharge, (WinScore + LostScore) Score,ServiceFee,UserID')
            ->get();
    }

    // 用户输赢
    public function accountWinLost($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPRecordDB() . 'RecordUserTotalStatistics')
            ->whereIn('UserID', $UserIDs)
            ->selectRaw('IsNull(sum(WinScore),0) + IsNull(sum(LostScore),0) ChangeScore,UserID')
            ->groupBy('UserID')
            ->pluck('ChangeScore', 'UserID')->toArray();
    }

    // 用户上级ID
    public function accountSpreaderID($UserIDs)
    {
        return DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->whereIn('UserID', $UserIDs)
            ->pluck('GameID', 'UserID')->toArray();
    }

    // 签到总奖励
    public function accountSignIn($where = [])
    {
        $Sql = DB::table(TableName::QPAccountsDB() . 'UserSignInInfo');
        !empty($where) && $Sql = $Sql->where($where);
        return $Sql->selectRaw('IsNull(sum(TotalReward),0) TotalReward')->first()->TotalReward / NumConfig::NUM_VALUE ?? 0;
    }

    // 获取用户ID
    public static function GetUserID(int $GameID)
    {
        return DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->where('GameID', $GameID)
            ->value('UserID');
    }


    // 获取用户 GAMEID
    public static function GetUserGameID(int $UserID)
    {
        return DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->where('UserID', $UserID)
            ->value('GameID');
    }

    // 用户手机号
    public function getUserPhone($UserID)
    {
        return DB::table(TableName::QPAccountsDB() . 'AccountPhone')->where('UserID', $UserID)->value('PhoneNum');
    }

    // 用户上级ID
    public function getSpreaderID($UserID)
    {
        return self::query()->where('UserID', $UserID)->value('SpreaderID');
    }

    // 游戏时长转换
    public function getUserPlayTimeCount($PlayTimeCount)
    {
        $second = $PlayTimeCount;
        $day = floor($second / (3600 * 24));
        $second = $second % (3600 * 24);//除去整天之后剩余的时间
        $hour = floor($second / 3600);
        $second = $second % 3600;//除去整小时之后剩余的时间
        $minute = floor($second / 60);
        //返回字符串
        return $day . '天' . $hour . '小时' . $minute . '分';
    }

    // 用户有没有在房间
    public function getUserOnLine($UserID)
    {
        // 有没有在房间
        return DB::connection('read')->table('QPTreasureDB.dbo.GameScoreLocker as gsl')
                ->join('QPPlatformDB.dbo.GameRoomInfo as gri', 'gsl.ServerID', 'gri.ServerID')
                ->where('UserID', $UserID)
                ->whereRaw('datediff(hh,CollectDate,getdate())<=5')
                ->select('ServerName')
                ->first()->ServerName ?? '';
    }

    // 用户未领邮件金额
    public function waitGetEmailScore($UserID)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.PrivateMail')
            ->where('UserID', $UserID)
            ->where('MailStatus', '<', 3)
            ->where('BonusString','<>','')
            ->sum('amount');
    }

    // 用户月卡【购买、已领】
    public function monthCard($UserID)
    {
        // 已买
        $buyMonthCard = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard as mc')
            ->join('QPPlatformDB.dbo.UserMonthCard as uc', 'mc.CardID', 'uc.CardID')
            ->where('uc.UserID', $UserID)
            ->selectRaw('IsNull(sum(Price),0) TotalReward')
            ->value('TotalReward');
        // 已领
        $getMonthCard = DB::connection('read')->table('QPPlatformDB.dbo.UserMonthCard')
            ->where('UserID', $UserID)
            ->selectRaw('IsNull(sum(TotalReward),0) Reward')
            ->value('Reward');
        return [$buyMonthCard, $getMonthCard];
    }

    // 关联注册IP数量
    public function sameRegisterIPCount($RegisterIP)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->where('RegisterIP', $RegisterIP)
            ->count();
    }

    public function samePhoneCount($phone)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountPhone')
            ->where('PhoneNum', $phone)
            ->count();
    }


    // 关联注册IP数量
    public function sameRegisterIPCountByUserID($UserID)
    {
        return 0;
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo as di')
                 ->join('QPAccountsDB.dbo.AccountsInfo as dif', 'di.RegisterIP', 'dif.RegisterIP')
                 ->where('di.UserID', $UserID)
                 ->selectRaw('count(1) countip')
                 ->first()->countip;
    }
    //关联提现名称
    public function sameWithDrawBankName($name){
        if($name==='')return 0;
        return DB::table(TableName::QPAccountsDB() . 'AccountWithDrawInfo')
                    ->select('UserID')
                    ->where("BankUserName",$name)
                    ->count();


    }
    //关联提现mail
    public function sameWithDrawEmail($email){
        if($email==='')return 0;
        return DB::table(TableName::QPAccountsDB() . 'AccountWithDrawInfo')
                    ->select('UserID')
                    ->where("EmailAddress",$email)
                    ->count();

    }
    //关联提现名称
    public function sameWithDrawBankNameByUserID($UserID){


        return DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo as di')
                 ->join('QPAccountsDB.dbo.AccountWithDrawInfo as dif', 'di.BankUserName', 'dif.BankUserName')
                 ->where('di.UserID', $UserID)
                 ->whereNotNull('di.BankUserName')
                 ->where('di.BankUserName', '<>', '')
                 ->selectRaw('count(1) count,di.BankUserName')
                 ->groupBy('di.BankUserName')
                 ->first();


    }
    //关联提现mail
    public function sameWithDrawEmailByUserID($UserID){
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo as di')
                 ->join('QPAccountsDB.dbo.AccountWithDrawInfo as dif', 'di.EmailAddress', 'dif.EmailAddress')
                 ->where('di.UserID', $UserID)
                 ->whereNotNull('di.EmailAddress')
                 ->where('di.EmailAddress', '<>', '')
                 ->selectRaw('count(1) count,di.EmailAddress')
                 ->groupBy('di.EmailAddress')
                 ->first();

    }
    // 关联登录IP数量
    public function sameLoginIPCount($UserID)
    {
        return DB::connection('read')->table('QPRecordDB.dbo.RecordUserLogonStatistics as a')
            ->join('QPRecordDB.dbo.RecordUserLogonStatistics as b', 'a.LogonIP', 'b.LogonIP')
            ->where('a.UserID', $UserID)
            ->selectRaw('count(distinct(b.UserID)) countIP')
            ->first()->countIP;
    }


    // 关联登录IP数量
    public function sameLoginMacCount($UserID)
    {

        $mac = DB::connection('read')->table('QPRecordDB.dbo.RecordUserLogonStatistics')
                 ->where('UserID', $UserID)
                 ->where('mac', '<>','')
                  ->selectRaw("distinct(mac) mac")
                 ->pluck('mac')->toArray();

        if($mac) {
            return DB::connection('read')->table('QPRecordDB.dbo.RecordUserLogonStatistics')
                     ->whereIn('mac', $mac)
                     ->selectRaw('count(distinct(UserID)) countIP')
                     ->first()->countIP;
        }
        return 0;
    }


    // 关联银行卡
    public function sameBankNo($UserID)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo as di')
            ->join('QPAccountsDB.dbo.AccountWithDrawInfo as dif', 'di.BankNo', 'dif.BankNo')
            ->where('di.UserID', $UserID)
            ->whereNotNull('di.BankNo')
            ->where('di.BankNo', '<>', '')
            ->selectRaw('count(1) count,di.BankNo')
            ->groupBy('di.BankNo')
            ->first();
    }

    // 用户彩金
    public function UserScoreChange($UserID, $Reason, $Type = 'string')
    {
        $Sql = DB::connection('read')->table('QPRecordDB.dbo.RecordUserScoreChange')
            ->where('UserID', $UserID);

        if ($Type == 'string') {
            $Sql->where('Reason', $Reason);
        } else {
            $Sql->whereIn('Reason', $Reason);
        }

        return $Sql->selectRaw('sum(ChangeScore) Score')
            ->first()->Score;
    }
}