sports_admin/app/Models/Account.php

<?php

namespace App\Models;

use Illuminate\Support\Facades\Log;
use DB;

/**
 *
 */
class Account extends BaseModel
{
    protected $table = 'account';
    public $timestamps = false;

    function account($list, $page, $where = '', $field = 'account.id', $ads = 'desc', $rflag, $tflag)
    {
        // DB::connection()->enableQueryLog();

        $rechargestatus = ' WHERE a.status=1 AND a.trade_type IN (6,12,11,14) ';
        $takestatus = ' WHERE b.status=1 AND b.trade_type=5';
        $rnum = ($rflag == -1) ? '' : ',mc.recharge_num';
        $tnum = ($tflag == -1) ? '' : ',tc.take_num';
        $rjoin = ($rflag == -1) ? '' : ' LEFT JOIN (SELECT account_identity,count("id") AS recharge_num FROM money_details a ' . $rechargestatus . ' GROUP BY a.account_identity) AS mc ON mc.account_identity=account.identity ';
        $tjoin = ($tflag == -1) ? '' : ' LEFT JOIN (SELECT account_identity,count(id) AS take_num FROM money_details d ' . $takestatus . ' GROUP BY d.account_identity) AS tc ON tc.account_identity=account.identity ';
        $agent_join = " LEFT JOIN nagent_detailed c ON b.parent_id=c.agent_identity";

        $sql = "SELECT account.id, account.identity,account, b.grade,c.agent_identity,c.agent_user as agent_name, b.cash, b.name, b.register_time, b.qq,b.wechat,b.register_ip, b.last_ip, b.phone, b.email, b.statuss,open_invitation,b.register_url,b.last_url,c.invite,to_char(last_time,'yyyy-mm-dd hh24:mi:ss') as last_time,account.status,b.statuss,b.parent_id" . $rnum . $tnum . " FROM account LEFT JOIN account_detailed b ON account.identity=b.account_identity" . $rjoin . $tjoin . $agent_join . " WHERE account.status<>4 " . $where . " ORDER BY " . $field . ' ' . $ads;
        if (!empty($list)) {
            $offset = $list * ($page - 1);
            $sql .= ' LIMIT ' . $list . ' OFFSET ' . $offset;
        }

        $countsql = "SELECT count(account.identity) FROM account INNER JOIN account_detailed b ON account.identity=b.account_identity " . $rjoin . $tjoin . $agent_join . " WHERE status<>4 " . $where;


        Log::info ($sql);


        $data = DB::select ($sql);

        $total = DB::select ($countsql);
        // $queries = DB::getQueryLog();
        // print_r($queries);
        if (!$data < 0) {
            return -2020052003; //没有用户会员
        }
        $data = json_encode ($data);
        $data = json_decode ($data, 1);
        $res = array('data' => $data, 'total' => $total[0]->count);

        return $res;
    }
    /*
    $offset = $list*($page-1);
        $rechargestatus = ' WHERE a.status=1 AND a.trade_type IN (6,12,11,14) ';
        $takestatus = ' WHERE b.status=1 AND b.trade_type=5';

        $sql = "SELECT account.id, account.identity, account, grade, cash, name, register_time, register_ip, last_ip, phone, email, statuss,to_char(last_time,'yyyy-mm-dd hh24:mi:ss') as last_time,status,mc.recharge_num,tc.take_num,ta.token_time,ta.effective_time FROM account".' INNER JOIN account_detailed ON account.identity=account_detailed.account_identity LEFT JOIN (SELECT account_identity,count("id") AS recharge_num FROM money_details a '.$rechargestatus.' GROUP BY a.account_identity) AS mc ON mc.account_identity=account.identity LEFT JOIN (SELECT account_identity,count(id) AS take_num FROM money_details b '.$takestatus.' GROUP BY b.account_identity) AS tc ON tc.account_identity=account.identity LEFT JOIN (SELECT last_time AS token_time,effective_time,account_identity FROM account_token) AS ta ON ta.account_identity=account.identity WHERE status<>4 '.$where.' ORDER BY '.$field.' ' .$ads.' LIMIT '.$list.' OFFSET '.$offset;

        $countsql = 'SELECT count(account.identity) FROM account INNER JOIN account_detailed ON account.identity=account_detailed.account_identity LEFT JOIN (SELECT account_identity,count("id") AS recharge_num FROM money_details a '.$rechargestatus.' GROUP BY a.account_identity) AS mc ON mc.account_identity=account.identity LEFT JOIN (SELECT account_identity,count(id) AS take_num FROM money_details b '.$takestatus.' GROUP BY b.account_identity) AS tc ON tc.account_identity=account.identity LEFT JOIN (SELECT last_time AS token_time,effective_time,account_identity FROM account_token) AS ta ON ta.account_identity=account.identity WHERE status<>4 '.$where;
        $data = DB::select($sql);
        $total = DB::select($countsql);

        if (!$data < 0) {
            return -2020052003; //没有用户会员
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        $res = array('data'=>$data,'total'=>$total[0]->count);

        return $res;
    */
    /*function account($list, $page, $where = '',$field='account.id',$ads='desc',$rechargenum='',$takenum='') {
        //$data = $this->join('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')
        //	->select('account.id', 'account.identity', 'account', 'grade', 'cash', 'name', 'register_time', 'register_ip', 'last_ip', 'phone', 'email', 'statuss','last_time','status');
        $wherestr = '';
        if(!empty($where)&&is_array($where)){
            foreach($where as $v){
                $wherestr .= ' and ' . implode(' ',$v);
            }
        }
        $offset = $list*($page-1);
        $rechargestatus = ' where a.status=1 and a.trade_type in (6,12,11,14) ';
        $takestatus = ' where b.status=1 and b.trade_type=5';
        //$rechargestatus = empty($rechargenum)?'':' where a.status=1 and trade_type in (6,12,11,14) ';
        //$takestatus = empty($takenum)?'':' where b.status=1 and trade_type=5';
        $sql = 'select account.id, account.identity, account, grade, cash, name, register_time, register_ip, last_ip, phone, email, statuss,last_time,status,mc.recharge_num,tc.take_num from account inner join account_detailed on account.identity=account_detailed.account_identity left join (select account_identity,count("id") as recharge_num from money_recharge a '.$rechargestatus.' GROUP BY a.account_identity) as mc on mc.account_identity=account.identity left join (select account_identity,count(id) as take_num from money_take b '.$takestatus.' GROUP BY b.account_identity) as tc on tc.account_identity=account.identity where status<>4 '.$wherestr.$rechargenum.$takenum.' order by '.$field.' ' .$ads.' limit '.$list.' offset '.$offset;
        //print_r($sql);
        $countsql = 'select count(account.identity) from account inner join account_detailed on account.identity=account_detailed.account_identity left join (select account_identity,count("id") as recharge_num from money_recharge a '.$rechargestatus.' GROUP BY a.account_identity) as mc on mc.account_identity=account.identity left join (select account_identity,count(id) as take_num from money_take b '.$takestatus.' GROUP BY b.account_identity) as tc on tc.account_identity=account.identity where status<>4 '.$wherestr.$rechargenum.$takenum;

        $data = DB::select($sql);
        $total = DB::select($countsql);

        //if (is_array($where)) {
        //	$where[] = array('status','<>',4);
        //	$data = $data->where($where);
        //}else{
        //	$data = $data->where('status', 1);
        //}
        //$data = $data->orderBy($field, $ads)->paginate($list);
        //$data = $data->paginate($list);
        //$account = $data->limit($list)->offset($page)->count();
        //$data = $data->limit($list)->offset($page)->get();

        //$queries = DB::getQueryLog();
        //print_r($queries);
        if (!$data < 0) {
            return -2020052003; //没有用户会员
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        $res = array('data'=>$data,'total'=>$total[0]->count);

        //print_r($res);
        return $res;
    }*/

    //查找代理id
    /**
     * 试玩账号
     * [FunctionName description]
     * @param string $value [description]
     */
    function demo_account($list = 10, $page, $where = '')
    {
        if (is_array ($where) && count ($where) > 0) {
            $where[] = array('status', '=', 4);
            $data = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')
                ->select ('account.id', 'account.identity', 'account', 'grade', 'cash', 'name', 'register_time', 'register_ip', 'last_ip', 'phone', 'email', 'statuss')
                ->where ($where)
                ->paginate ($list);
        } else {
            $data = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')
                ->select ('account.id', 'account', 'register_time', 'cash', 'last_ip', 'register_ip', 'statuss')
                ->where ('status', 4)
                ->paginate ($list);
        }
        if (!$data < 0) {
            return -2021052003; //没有用户会员
        }
        return $data->toArray ();
    }

    //获取用户详情
    function getUserDetails($value, $type = 1, $jointable = '', $columnn = '', $columnw = '', $page = 1, $limit = 10, $sort = 'id', $ads = 'asc')
    {
        $key = $this->getFeild ($type);
        $sort = 'account.' . $sort;
        if (empty($jointable)) {
            if (is_array ($value)) {
                $data = $this->where ($value);
            } else {
                $data = $this->where ($this->table . '.' . $key, $value);
            }
        } elseif ($jointable == 'account_detailed') {
            if (is_array ($value)) {
                $data = $this->select ('account.id', 'account as account_name', 'account.identity', 'cash')->where ($value)->join ($jointable, $this->table . '.' . $columnn, $jointable . '.' . $columnw);
            } else {
                $data = $this->select ('account.id', 'account as account_name', 'account.identity', 'cash')->where ($this->table . '.' . $key, $value)->join ($jointable, $this->table . '.' . $columnn, $jointable . '.' . $columnw);
            }
        } else {
            $data = $this->where ($this->table . '.' . $key, $value)->join ($jointable, $this->table . '.' . $columnn, $jointable . '.' . $columnw);
        }

        $data = $data->orderby ($sort, $ads)->paginate ($limit);

        if (!$data) {
            return -30203000202; //没有用户详情
        }
        return $data->toArray ();
    }

    //会员汇总统计
    function userCount($where = '')
    {
        $data = array();
        $data['allcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ('status', '<>', 4)->sum ('cash');
        $data['normalcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ('status', 1)->sum ('cash');
        $data['stopcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ('status', -1)->sum ('cash');
        $data['speccash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ('status', -2)->sum ('cash');
        if (!empty($where) && is_array ($where)) {
            $data['allcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ($where)->where ('status', '<>', 4)->sum ('cash');
            $data['normalcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ($where)->where ('status', 1)->sum ('cash');
            $data['stopcash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ($where)->where ('status', -1)->sum ('cash');
            $data['speccash'] = $this->join ('account_detailed', 'account.identity', '=', 'account_detailed.account_identity')->where ($where)->where ('status', -2)->sum ('cash');
        }
        return $data;
    }

    //字段对应值
    private function getFeild($num)
    {
        $data = array(
            '1' => 'id',
            '2' => 'identity',
            '3' => 'account',
            '4' => 'status',
        );
        return $data[$num];
    }

    function getinfo($value, $type = 1)
    {
        $key = $this->getFeild ($type);
        $data = $this->where ($key, $value)->first ();
        if (!$data) {
            return -30203000202; //没有用户详情
        }
        return $data->toArray ();
    }

    protected function getinfoCopy($value, $type = 1)
    {
        return $this->getinfo ($value, $type);
    }

    //更新
    function updateInfo($data, $id)
    {
        $res = $this->where ('id', $id)->update ($data);
        if (!$res) {
            return -7010101202; //更新失败
        }
        return 1;
    }

    //更新
    function upUserInfo($data, $id)
    {
        $res = $this->where ('identity', $id)->update ($data);
        if (!$res) {
            return -7010101202; //更新失败
        }
        return 1;
    }

    //获取用户数组
    function getUserData($data)
    {
        $datas = $this->select ('account', 'identity')->whereIn ('account', $data)->get ();
        if (!$datas) {
            return -502030154202; //没有
        }
        return $data->toArray ();
    }

    function Accounta($where)
    {
        if (empty($where)) {
            return '-502030154202';
        }
        $data = $this->where ('account', $where['account'])->where ('status', $where['status'])->first ();
        if (!$data) {
            return -502030154202;
        }
        return $data->toArray ();
    }

    function Accountas()
    {

        $data = $this->select ('account', 'id')->orderBy ('id', 'desc')->get ();
        if (!$data) {
            return -502030154202;
        }
        return $data->toArray ();
    }

    function Accountad($id)
    {

        $data = $this->select ('account', 'id')->where ('id', "<", $id)->orderBy ('id', 'desc')->limit (50)->get ();
        if (!$data) {
            return -502030154202;
        }
        return $data->toArray ();
    }

    //获取会员等级
    function getUserGrade()
    {
        $data = db::table ('dc_user_grade')->select ('name', 'id', 'grade')->orderBy ('grade', 'desc')->get ();
        if (!$data) {
            return -502030154202;
        }
        $data = $data->toArray ();
        $arr = array();
        foreach ($data as $key => $value) {
            $arr[$value->grade] = $value->name;
        }
        return $arr;
    }

    /**
     * 首充检查
     * @param string $account_identity
     * @param string $type
     * @return mixed
     */
    public static function isFirstRecharge(string $account_identity, string $type = '')
    {
        $m = new Money_recharge();
        $where = [
            'account_identity' => $account_identity
        ];
        switch ($type) {
            case 'month':
                $where[] = ['complete_time', '>=', (date ('Y-m-1'))];
                break;
            case 'week':
                $time = date ('Y-m-d');
                $t = strtotime ($time . ' Sunday');
                $where[] = ['complete_time', '>=', date ('Y-m-d H:i:s', $t - 86400 * 6)];
            default:
        }
        return $num = $m->where ($where)->count ('money');
    }

    public static function checkWithdraw($account_identity)
    {
        //$withdrawRule=new Withdraw_rule();
        return Withdraw_rule::check ($account_identity);

    }

    //根据用户名获取用户信息 2019-019-23 anton
    public function checkUserByName($username){
        $uinfo =  $this->leftjoin('account_detailed', 'account_detailed.account_identity', '=', 'account.identity')
            ->select('account_detailed.cash')
            ->where('account.account', '=', $username)
            ->first();
        if(!$uinfo){
            return -502030154202;
        }
        return $uinfo->toArray();
    }

    //获取用户名信息 2019-019-26 anton
    public function getUserInfo($rp_name){
        $uinfo =  $this->leftjoin('oggame_user', 'oggame_user.lo_name', '=', 'account.account')
            ->select('account.identity','account.account')
            ->where('oggame_user.rp_name', '=', $rp_name)
            ->first();
        if(!$uinfo){
            return -502030154202;
        }
        return $uinfo->toArray();
    }


}