<?php


namespace App\Models;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

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

    public $timestamps = false;

    // 提现订单查询 excel导出
    public static function orderOverDownExcel($query, $where)
    {
        $order = ['OrderId', 'ai.GameID', 'ai.Channel','ow.BankUserName','ow.AccountsBank','ow.EmailAddress','ow.AdhaarNumber','PhoneNumber','IFSCNumber','PixNum','PixType','CreateDate','WithDraw','ServiceFee','ow.State',
            'agent','finishDate','locking']; 
        $list = $query
            ->where($where)
            ->select($order)
            ->orderBy('finishDate','desc' )
            ->get();

        $agentName = DB::connection('write')->table('agent.dbo.admin_configs')
            ->where('type','cash')
            ->pluck('name', 'id')->toArray();
        $Withdrawal_M = new Withdrawal();
        foreach ($list as &$val) {

            if ($val->PixType < 10) {
                unset($val->AccountsBank);
                unset($val->AdhaarNumber);
                unset($val->IFSCNumber);
            }else{
                unset($val->BankUserName);
                unset($val->EmailAddress);
                unset($val->PhoneNumber);
            }
            if ($val->PixType == 1) {
                $val->PixType = 'CPF';
            }elseif ($val->PixType == 2){
                $val->PixType = 'Email';
            }elseif ($val->PixType == 3){
                $val->PixType = 'Phone';
            }else{
                $val->PixType = '银行卡';
            }
            $DrawTotal = number_float(($val->WithDraw + $val->ServiceFee) / 100); // 实际提现金额
            $val->ServiceFee = number_float($val->WithDraw / 100);
            $val->WithDraw = $DrawTotal;

            $val->State = $Withdrawal_M->stateText($val->State, $val->locking);
            $val->agent = isset($agentName[$val->agent]) ? $agentName[$val->agent] : '';
            unset($val->locking);
        }

        $title = ['订单号', '玩家ID', '渠道', '提款名字', '邮箱', '电话', 'CPF/CNPJ', '提款方式', '提交时间', '提现申请额度', '实际到账金额', '审核状态','代付方', '订单完成时间'];
        $list = json_decode(json_encode($list), true);
        downloadExcel($list, $title, '提现订单查询' . date('YmdHis'));
    }

    // 订单审核 Excel导出
    public static function verifyOrderDownExcel($query, $where)
    {
//        $order = ['OrderId', 'ai.GameID', 'ai.Channel', 'ai.NickName', 'BankUserName', 'BankNO', 'ow.CreateDate', 'WithDraw', 'ServiceFee', 'ow.State', 'ai.UserID', 'remarks'];

        $order = ['OrderId', 'ai.GameID', 'ai.Channel','ow.BankUserName','ow.AccountsBank','ow.EmailAddress','ow.AdhaarNumber','PhoneNumber','IFSCNumber','PixNum','PixType','CreateDate','WithDraw','ServiceFee','ow.State',
            'agent', 'finishDate','locking'];
        $list = $query
            ->where($where)
            ->select($order)
            ->orderBy('CreateDate','asc' )
            ->get();

        $Withdrawal_M = new Withdrawal();
        foreach ($list as &$val) {

            if ($val->PixType < 10) {
                unset($val->AccountsBank);
                unset($val->AdhaarNumber);
                unset($val->IFSCNumber);
            }else{
                unset($val->BankUserName);
                unset($val->EmailAddress);
                unset($val->PhoneNumber);
            }
            if ($val->PixType == 1) {
                $val->PixType = 'CPF';
            }elseif ($val->PixType == 2){
                $val->PixType = 'Email';
            }elseif ($val->PixType == 3){
                $val->PixType = 'Phone';
            }else{
                $val->PixType = '银行卡';
            }

            $DrawTotal = number_float(($val->WithDraw + $val->ServiceFee) / 100); // 实际提现金额
            $val->ServiceFee = number_float($val->WithDraw / 100);
            $val->WithDraw = $DrawTotal;

            $val->State = $Withdrawal_M->stateText($val->State, $val->locking);

            unset($val->locking);
        }
        $title = ['订单号', '玩家ID', '渠道', '提款名字', '邮箱', '电话', 'CPF/CNPJ', '提款方式', '提交时间', '提现申请额度', '实际到账金额', '审核状态','代付方', '订单完成时间','风险提示'];
        $list = json_decode(json_encode($list), true);
        downloadExcel($list, $title, '提现订单审核' . date('YmdHis'));
    }

    // 总申请金额
    public function TotalWithDraw($where, $type = '')
    {
        $query = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw as ow')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ow.UserID', 'ai.UserID')
            ->leftJoin('agent.dbo.withdrawal_position_log as log', function ($join) {
                $join->on('log.order_sn', '=', 'ow.OrderID');
                $join->where('log.take_effect', 2);
            })
            ->leftJoin('QPRecordDB.dbo.RecordPackageName as rn', 'ai.UserID', 'rn.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountsRecord as ar', function ($join) {
                $join->on('ow.RecordID', 'ar.RecordID');
                $join->where('type', 1);
            });

        switch ($type) {
            case 1: # 已充值
                $query->join('QPAccountsDB.dbo.YN_VIPAccount as vip', 'ow.UserID', 'vip.UserID');
                break;
            case 2: # 未充值
                $query->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as vip', 'ow.UserID', 'vip.UserID')->whereNull('vip.UserID');
                break;
        }
        return $query
            ->where($where)
            ->selectRaw('sum(WithDraw+ServiceFee) as WithDraw,count(distinct(ow.UserID))count_U,count(ow.RecordID) count_ID')
            ->first();
    }

    // 银行卡绑定信息评估
    public function BankNO($userIDs)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo as di')
            ->join('QPAccountsDB.dbo.AccountWithDrawInfo as dif', 'di.BankNo', 'dif.BankNo')
            ->whereIn('di.UserID', $userIDs)
            ->whereNotNull('di.BankNo')
            ->selectRaw('count(1) count,di.UserID')
            ->groupBy('di.UserID')
            ->pluck('count', 'UserID')->toArray();
    }

    public function ip($userIDs)
    {
        return DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo as ai')
            ->join('QPAccountsDB.dbo.AccountsInfo as ain', 'ai.RegisterIP', 'ain.RegisterIP')
            ->whereIn('ai.UserID', $userIDs)
            ->whereNotNull('ai.RegisterIP')
            ->selectRaw('count(1) count,ai.GameID')
            ->groupBy('ai.GameID')
            ->pluck('count', 'GameID')->toArray();
    }

}