sports_admin/app/Models/MoneyBuy.php

<?php
namespace App\Models;

use DB;

class MoneyBuy extends BaseModel
{

    protected $table = "money_buy";
    public $timestamps = false;
    //获取注单信息
    public function getBuyInfo($game, $list = 20, $fild = 'money_time', $order = 'desc', $where = '', $page = 1)
    {
        $frist = ($page - 1) * $list;
//        DB::connection()->enableQueryLog();
        if (!empty($where) && count($where) > 0) {
            $model = $this->select($this->table . '.id', 'account_name', 'order_id', 'game_name', 'game_no', 'money_time', 'money', 'get_money', $this->table . '.codes', $this->table . '.status', 'game_status', 'extra', 'prizes', 'game_' . $game . '.codes as prizes_code', 'prize', 'remark')
                ->join('game_' . $game, DB::raw("cast(info_no as VARCHAR)"), 'game_no')
                ->where($where)
                ->orderBy('money_time', $order)
                ->orderBy('game_no', $order);
        } else {
            $model = $this->select($this->table . '.id', 'account_name', 'order_id', 'game_name', 'game_no', 'money_time', 'money', 'get_money', $this->table . '.codes', $this->table . '.status', 'game_status', 'extra', 'prizes', 'game_' . $game . '.codes as prizes_code', 'remark')
                ->join('game_' . $game, DB::raw("cast(info_no as VARCHAR)"), 'game_no')
                ->where('game_name', $game)
                ->orderBy('money_time', $order)
                ->orderBy('game_no', $order);
        }
        if (!empty($list)) {
            $model = $model->paginate($list);
        } else {
            $model = $model->get();
        }

        if (!$model) {
            return -5030000122; //没有数据
        }
        return $model->toArray();
    }

    //获取异常注意信息
    public function getBtrecordBuyInfo($game, $list = 20, $fild = 'money_time', $order = 'desc', $where = '', $page = 1)
    {
        $frist = ($page - 1) * $list;
        DB::connection()->enableQueryLog();
        if ($game == 'threessc' || $game == 'jspk10') {
            $model = $this->select($this->table . '.id', 'account_name', 'order_id', 'game_name', intval('game_no'), 'money_time', 'money', 'get_money', $this->table . '.codes', $this->table . '.status', 'game_status', 'extra', 'prizes', 'game_' . $game . '.codes as prizes_code', 'prize', 'type')
            // ->join('game_' . $game, 'info_no', intval('game_no'))
                ->join('btrecord', 'btrecord.identity', 'money_buy.info_identity')
                ->where($where)
                ->orderBy('money_time', $order);
        } else {
            if (!empty($where) && count($where) > 0) {
                $model = $this->select($this->table . '.id', 'account_name', 'order_id', 'game_name', 'game_no', 'money_time', 'money', 'get_money', $this->table . '.codes', $this->table . '.status', 'game_status', 'extra', 'prizes', 'game_' . $game . '.codes as prizes_code', 'prize', 'type')
                    ->join('game_' . $game, 'info_no', 'game_no')
                    ->join('btrecord', 'btrecord.identity', 'money_buy.info_identity')
                    ->where($where)
                    ->orderBy('money_time', $order);
            } else {
                $model = $this->select($this->table . '.id', 'account_name', 'order_id', 'game_name', 'game_no', 'money_time', 'money', 'get_money', $this->table . '.codes', $this->table . '.status', 'game_status', 'extra', 'prizes', 'game_' . $game . '.codes as prizes_code', 'type')
                    ->join('game_' . $game, 'info_no', 'game_no')
                    ->join('btrecord', 'btrecord.identity', 'money_buy.info_identity')
                    ->where('game_name', $game)
                    ->orderBy('money_time', $order);
            }
        }
        if (!empty($list)) {
            $model = $model->paginate($list);
        } else {
            $model = $model->get();
        }

        if (!$model) {
            return -5030000122; //没有数据
        }
        return $model->toArray();
    }

    //根据订单号获取订单信息
    public function getInfoByOrderID($order_id)
    {
        $data = $this->where('order_id', $order_id)->first();
        if (!$data) {
            return -5030001022; //没有订单数据
        }
        return $data->toArray();
    }

    //获取注单信息
    public function getBetInfo($where)
    {
        $data = $this->select('codes')->where($where)->get();
        if (!$data) {
            return -503000102; //没有订单数据
        }
        return $data->toArray();
    }

    //根据id获取订单信息
    public function getInfoByID($id)
    {
        $data = $this->where('id', $id)->first();
        if (!$data) {
            return -5030001022; //没有订单数据
        }
        return $data->toArray();
    }

    public function getTotal($list = 10, $page, $where = '', $having = '')
    {
//        DB::connection()->enableQueryLog();
        if (!empty($where) && is_array($where) && !empty($having) && is_array($having)) {
            $data = $this->where($where)->select(DB::raw('sum("get_money") as winmoney,sum("prize_money") as prizemoney,sum("money") as betmoney,game_name,count("order_id") as munBet,max("money_time") as end_time,min("money_time") as star_time,game_status'))
                ->join('account','money_buy.account_identity', '=', 'account.identity')
                ->where('account.status','<>','4')
                ->where('account.user_type','1')
                ->whereIn('status', array(1, 2))
                ->groupBy('game_name')
                ->groupBy('game_status');
            foreach ($having as $v) {
                $data = $data->havingRaw($v);
            }
            $data = $data->orderby('star_time', 'desc')->get();
        } elseif (!empty($having) && is_array($having)) {
            $data = $this->select(DB::raw('sum("get_money") as winmoney,sum("prize_money") as prizemoney,sum("money") as betmoney,game_name,count("order_id") as munbet,max("money_time") as end_time,min("money_time") as star_time,game_status'))
                ->join('account','money_buy.account_identity', '=', 'account.identity')
                ->where('account.status','<>','4')
                ->where('account.user_type','1')
                ->whereIn('status', array(1, 2))
                ->groupBy('game_name')
                ->groupBy('game_status');
            foreach ($having as $v) {
                $data = $data->havingRaw($v);
            }
            $data = $data->orderby('star_time', 'desc')->get();
        } elseif (!empty($where) && is_array($where)) {
            $data = $this->where($where)->select(DB::raw('sum("get_money") as winmoney,sum("prize_money") as prizemoney,sum("money") as betmoney,game_name,count("order_id") as munBet,max("money_time") as end_time,min("money_time") as star_time,game_status'))->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->groupBy('game_status')->groupBy('game_name')->orderby('star_time', 'desc')->get();
        }
        //$queries = DB::getQueryLog();
        //print_r($queries);
        if (!isset($data) || !$data) {

        } else {
            $data = $data->toArray();
            $gamename = trans('report');
            foreach ($data as $k => $v) {
                $data[$k]['name'] = $gamename[$v['game_name']];
                $data[$k]['munBet'] = $v['munbet'];
            }
            return $data;
        }

        $fs = ($page - 1) * $list;
        $array = array();
        $data = DB::select('select sum("get_money") as "winmoney",sum("money") as "betmoney","game_name",count("order_id") as "munBet",max("money_time") as end_time,min("money_time") as star_time from money_buy inner join account on account.identity=account_identity where money_buy.status != 4 and account.user_type=1 group by game_name ');
        foreach ($data as $k => $v) {
            $array[$k]['winmoney'] = $v->winmoney;
            $array[$k]['betmoney'] = $v->betmoney;
            $array[$k]['game_name'] = $v->game_name;
            $array[$k]['munBet'] = $v->munBet;
            $array[$k]['star_time'] = $v->star_time;
            $array[$k]['end_time'] = $v->end_time;
            $array[$k]['prizemoney'] = $v->winmoney + $v->betmoney;
            $array[$k]['name'] = trans('report.' . $v->game_name);
        }
//        $count = DB::select('select sum("get_money") as "winmoney",sum("money") as "betmoney","game_name",count("order_id") as "munBet" from money_buy where status != 4 group by game_name');
        //        $array['total']=count($count);
        return $array;

    }

    public function getEffectiveMoney($list = 10, $page, $nodatas = '', $name = '')
    {
        $data = $this->whereIn('game_no', $nodatas)->select(DB::raw('sum("money") as money,game_name'))
            ->join('account','money_buy.account_identity', '=', 'account.identity')
            ->where('account.status','<>','4')
            ->where('account.user_type','1')
            ->whereIn('status', array(1, 2))
            ->where('game_status', '<>', 0);
        if (!empty($name)) {
            $data = $data->where('game_name', $name);
        }
        $data = $data->groupBy('game_name')
            ->get();
        return $data->toArray();
    }

    //获取回水列表
    public function getMoneyReturn($value = '', $type = 1, $limit = 10, $wheregame = '', $orwhere = '', $having = '')
    {
        DB::connection()->enableQueryLog();
        $key = $this->getFeild($type);
//        $sort = is_integer($sort) ? $this->getFeild($sort) : $sort;
        $data = $this->select(DB::raw('sum("get_money") as total_money,account_name,account_identity'))->groupBy('account_name', 'account_identity');

        if (!empty($having)) {
            $data = $data->havingRaw($having);
        }
        if (!empty($wheregame)) {
//            $data = $data->where(function ($data) use ($wheregame, $orwhere) {
            //                $data->where($orwhere)->orwhere($wheregame);
            //            });
            $data->whereIn('game_name', $wheregame);
        }
        if (empty($value) || is_array($value)) {
            $where = $value;
        } else {
            $where[] = array($key, '=', $value);
        }
//        $where[] = array('get_money', '<', '0');
        $where[] = array('water_status', '=', 1);
        $where[] = array('member_type', '!=', 4);
//        $where[] = array('total_money', '<', '0');
        //print_r($where);
        $data = $data->where($where);
        $data = $data->whereIn('status', [1, 2])->paginate($limit);

//        $queries = DB::getQueryLog();
        //        print_r($queries);
        if (!$data) {
            return -5030001202; //没有列表数据
        }
        return $data->toArray();
    }

    //获取用户回水详情列表
    public function getUserReturndetail($value = '', $type = 1, $limit = 10, $sort = 13, $ads = 'desc')
    {
        $key = $this->getFeild($type);
        $sort = is_integer($sort) ? $this->getFeild($sort) : $sort;
        $data = $this->orderby($sort, $ads);

        if (empty($value) || is_array($value)) {
            $value[] = array('water_status', '=', 1);
            $data = $data->where($value);
        } else {
            $data = $data->where($key, $value);
        }
        $data = $data->whereIn('status', [1, 2])->paginate($limit);

        if (!$data) {
            return -5030001402; //没有列表数据
        }
        $data = $data->toArray();
        foreach ($data['data'] as $k => $v) {
            $data['data'][$k]['game_name'] = trans('money.gametype.' . $v['game_name']);
            $data['data'][$k]['returnstatus'] = trans('status.money_buy.status')[$v['status']];
        }
        return $data;
    }

    //字段对应值
    private function getFeild($num)
    {
        $data = array(
            '1' => 'id',
            '2' => 'info_identity',
            '3' => 'order_id',
            '4' => 'account_name',
            '5' => 'account_identity',
            '6' => 'game_name',
            '7' => 'game_no',
            '8' => 'prize_note',
            '9' => 'money',
            '10' => 'prize_money',
            '11' => 'status',
            '12' => 'water_status',
            '13' => 'money_time',
            '14' => 'get_money',
            '15' => 'settle_status',
            '16' => 'game_status',
            '17' => 'member_type',
        );
        return $data[$num];
    }

    public function getBetMoney($time, $id)
    {
        // DB::connection()->enableQueryLog();
        $sum = $this->where('money_time', '>=', $time)->whereIn('status', ['1', '2'])->where('game_status', '<>', 0)->where('account_identity', $id)->sum('money');
        // $queries = DB::getQueryLog();
        // print_r($queries);
        return $sum;
    }

    public function getTotalByUser($game, $page, $list, $where = '')
    {
        if (!empty($where) && is_array($where)) {
            $where[] = array('status', '<>', 4);
            $where[] = array('game_status', '<>', '0');
            $where[] = array('game_name', '=', $game);
            $data = $this->select(DB::raw('sum("get_money") as "winmoney",sum("money") as "betmoney","account_name",count("money") as "munbet" ,max("money_time") as end_time,min("money_time") as star_time'))->where($where)->groupBy('account_name')->paginate($list);
            if (!$data) {
                return -5030001302; //没有列表数据
            }
            $data = $data->toArray();
            foreach ($data['data'] as $k => $v) {
                $data['data'][$k]['game_name'] = $game;
                $data['data'][$k]['munBet'] = $v['munbet'];
                $data['data'][$k]['name'] = trans('report.' . $game);
            }
            return $data;
        }
        $start = ($page - 1) * $list;
        $data = DB::select('select sum("get_money") as "winmoney",sum("money") as "betmoney","account_name",count("money") as "munBet" ,max("money_time") as end_time,min("money_time") as star_time from money_buy where status != 4  AND game_status <> 0 AND game_name=:game group by account_name offset :start limit :list', ['game' => $game, 'start' => $start, 'list' => $list]);

        $count = $this->where('game_name', $game)->where('status', '<>', 4)->where('game_status', '<>', 0)->groupBy('account_name')->count();
        $array = array();
        foreach ($data as $k => $v) {
            $array[$k]['winmoney'] = $v->winmoney;
            $array[$k]['betmoney'] = $v->betmoney;
            $array[$k]['munBet'] = $v->munBet;
            $array[$k]['star_time'] = $v->star_time;
            $array[$k]['end_time'] = $v->end_time;
            $array[$k]['name'] = trans('report.' . $game);
            $array[$k]['account_name'] = $v->account_name;
        }
        $datas['data'] = $array;
        $datas['total'] = $count;
        return $datas;
    }
    //获取投注失败的订单
    public function getNotBet($list = 50, $field = 'money_time', $order = 'desc')
    {
        $data = $this->where('status', 4)->orderBy($field, $order)->paginate($list);
        if (!$data) {
            return -5030000122; //没有数据
        }
        return $data->toArray();
    }
    //获取全部注单信息
    public function getBuy($where = '', $list = 20, $page = 1, $field = 'money_time', $order = 'desc')
    {
        if (is_array($where) && count($where)) {
            $data = $this->where($where)->orderBy($field, $order);
        } else {
            $data = $this->orderBy($field, $order);
        }
        $data = $data->orderBy('game_no', $order);
        
        if (!empty($list)) {
            $data = $data->paginate($list);
        } else {
            $data = $data->get();
        }

        if (!$data) {
            return -5030000222; //没有数据
        };
        return $data->toArray();
    }

    //获取用户打码量
    public function getHitcode($where)
    {
        //DB::connection()->enableQueryLog();
        $data = $this->select(DB::raw('sum("money") as "hitcode_count",account_identity,account_name'))->where($where)->groupBy('account_identity')->groupBy('account_name')->first();
        //$queries = DB::getQueryLog();
        //print_r($queries);
        if (!$data) {
            return -5030000402; //没有数据
        }
        return $data->toArray();
    }
    //反水状态
    public function updateWater($name, $timearea, $game_name = '')
    {

        $res = $this->where('account_name', $name)
            ->where($timearea);
        if (empty($game_name)) {
            $res->whereIn('game_name', array('xy28', 'jnd28'));
        } else {
            $res->where('game_name', $game_name);
        }

        $res = $res->update(['water_status' => '2']);

        if (!$res) {
            return -3012564406; //反水失败
        }
        return 1;
    }
    //总投注
    public function totalBet($where)
    {
        if (count($where) > 0) {
            return $this->where($where)->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->sum('money');
        } else {
            return $this->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->sum('money');
        }
    }
    //总赢取
    public function totalGetMoney($where)
    {
        if (count($where) > 0) {
            $res = $this->where($where)->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->sum('get_money');
        } else {
            $res = $this->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->sum('money');
        }
        return -$res;
    }

    //异常总投注
    public function totalBtBet($where)
    {
        if (count($where) > 0) {
            return $this->where($where)
                ->join('btrecord', 'btrecord.identity', 'money_buy.info_identity')->sum('money');
        } else {
            return $this->sum('money');
        }
    }
    //异常总赢取
    public function totalBtGetMoney($where)
    {
        if (count($where) > 0) {
            $res = $this->where($where)
                ->join('btrecord', 'btrecord.identity', 'money_buy.info_identity')->sum('get_money');
        } else {
            $res = $this->sum('money');
        }
        return -$res;
    }
    //撤单
    public function Cancel($where)
    {
        $res = $this->where($where)->where('game_status', 0)->update(['status' => 4]);
        if (!$res) {
            return -5051262522;
        }
        return 1;
    }
    //由id数组撤单
    public function CancelByids($ids)
    {
        $res = $this->whereIn('id', $ids)->update(['status' => 4]);
        if (!$res) {
            return -5051262522;
        }
        return 1;
    }

    //统计每个游戏今日总投注金额
    public function countTodayMoney()
    {
        $where = array(
            array('money_buy.status','<>','4'),
            array('account.status','<>','4'),
            array('account.user_type','=',1),
            array('money_time', '>=', date('Y-m-d 00:00:00', time())),
            array('money_time', '<=', date('Y-m-d 23:59:59', time())),
        );
        $data = $this->select(DB::raw('sum("money") as "betmoney",game_name'))->join('account','money_buy.account_identity', '=', 'account.identity')->where($where)->groupBy('game_name')->get();

        if (!$data) {
            return -5000100102;
        }
        return $data->toArray();
    }

    //统计7日总投注金额趋势
    public function countSevendayMoney($where)
    {
        $data = DB::select("select sum(money) as betmoney,  sum(get_money) as winfail_money, to_char(money_time, 'YYYY-MM-DD') as money_date from money_buy inner join account on account.identity=account_identity where (to_char(money_time, 'YYYY-MM-DD') >= '" . $where . "') and account.user_type=1 group by money_date");

        if (!$data) {
            return -5000100202;
        }
        $data = json_encode($data);
        $data = json_decode($data, 1);
        return $data;
    }

    //更新结算状态
    public function updateSettle($account_arr, $start_time, $end_time)
    {
        $model = $this->whereIn('account_name', $account_arr)
            ->where('game_status', '!=', 0);
        if (!empty($start_time)) {
            $model->where('money_time', '>=', $start_time);
        }
        if (!empty($end_time)) {
            $model->where('money_time', '<=', $end_time);
        }
        $res = $model->update(['settle_status' => 2]);
        if (!$res) {
            return -8005476322;
        }
        return 1;
    }
    //管理员作废更新
    public function updateStatus($order_id)
    {
        $res = $this->where('order_id', $order_id)->update(['status' => 4, 'prize_money' => 0, 'get_money' => 0]);
        if (!$res) {
            return -7021021122;
        }
        return 1;
    }
    //统计回水亏损金额用于计算
    public function countReturn($accountIDs)
    {
        $data = $this->select(DB::raw('sum("get_money") as total_money,account_name,max("account_identity") as account_identity'))
            ->whereIn('status', [1, 2])
            ->whereIn('game_name', array('xy28', 'jnd28'))
            ->whereIn('account_identity', $accountIDs)
            ->groupBy('account_name')
            ->get();
        if (!$data) {
            return -5030001202; //没有列表数据
        }
        return $data->toArray();
    }

    //代理下级用户投注金额，盈利统计统计
    public function getChildMoneyTotal($child_arr, $timeArea, $type = '')
    {
//         DB::connection()->enableQueryLog();
        $data = $this->select(DB::raw('sum(money) as money,sum(get_money) as get_money,account_name'))->whereIn('account_name', $child_arr);
        $data = $data->whereIn('status', array(1, 2));
        $data = $data->where('game_status', '<>', '0');
        if ($type == 1) { //未结算金额
            $data = $data->where('settle_status', 1);
        }
        if (!empty($timeArea) && count($timeArea) > 0) {
            $data = $data->where($timeArea);
        }
        $data = $data->groupBy('account_name');
        $data = $data->get();
//         $queries = DB::getQueryLog();
        //         print_r($queries);
        if (!$data) {
            return -8040410022; //没有统计数据
        }
        return $data->toArray();
    }

    //一段时间内的提现人数
    public function countUser($timearea)
    {
        $data = $this->select('account_name')->where('money_buy.status', '<>', 4)->join('account','money_buy.account_identity', '=', 'account.identity')->where('account.status','<>','4')->where('account.user_type','1')->whereBetween('money_time', $timearea)->groupBy('account_name')->get();
        if (!$data) {
            return 0;
        }
        $data = $data->toArray();
        return count($data);
    }
    //获取下级流水键代理id对流水金额
    public function getChildSumMoney($agent_identity_arr, $time1, $time2, $type = 1)
    {
//        DB::connection()->enableQueryLog();
        $data = $this->select(DB::raw('"sum"(money) as money,agent_identity'))
            ->leftJoin('agent_child', 'agent_child.account_identity', 'money_buy.account_identity')
            ->whereIn('agent_identity', $agent_identity_arr)
            ->whereIn('status', [1, 2])
            ->where('game_status', '<>', 0);
        if ($type != 1) {
            //已结算投注金额
            $data = $data->where('settle_status', 2);
        }
        if (!empty($time1)) {
            $data = $data->where('money_time', '>=', $time1);
        }
        if (!empty($time2)) {
            $data = $data->where('money_time', '<=', $time2);
        }
        $data = $data->groupBy('agent_identity')->get();
        if (!$data) {
            return -5000100202;
        }

//        $queries = DB::getQueryLog();
        //         print_r($queries);exit;
        //        print_r($data);
        if (empty($data)) {
            return -5000100202;
        }

//        $data = json_encode($data);
        //        $data = json_decode($data,1);
        $data = $data->toArray();
        $return_data = array();
        foreach ($data as $k => $v) {
            $return_data[$v['agent_identity']] = $v['money'];
        }
        return $return_data;

    }
    //统计用户当前期投注总金额
    public function countNoBetMoney($account_identity, $game_no, $game_name)
    {
        return $this->where('account_identity', $account_identity)->where('game_no', $game_no)
            ->where('status', '<', 4)->where('game_name', $game_name)->sum('money');
    }

    /**
     *查询历史数据
     */
    protected function getHistoryBet($limit)
    {
        $data = $this->where('status', '<>', 4)->where('game_status', '<>', 0)->paginate($limit);
        return $data->toArray();
    }

    protected function BetList($game, $no, $list)
    {
//                DB::connection()->enableQueryLog();
        if(strrpos($game, 'sixlottery') !== false){
            $data = \App\Models\SixMoneyBuy::BetList($game,$no,$list);
            return $data;
        }
        $data = $this->join('game_' . $game . '_buy', $this->table . '.info_identity', 'game_' . $game . '_buy.identity')
            ->leftJoin('money_back', function ($join) {
                $join->on('money_back.buy_identity', '=', $this->table . '.order_id')
                    ->where('money_back.status', '<>', 3);
            })
            ->where('game_no', $no)
            ->where('game_' . $game . '_buy.delstatus', 1)
//            ->where('money_back.status','<>',3)
            ->orderBy('game_' . $game . '_buy.account_identity')
            ->select('game_' . $game . '_buy.*', 'money_buy.account_name', 'money_back.order_id as back_orderid', 'money_back.money as back_money')
            ->paginate($list);
//        $queries = DB::getQueryLog();
        //        echo '<pre>';
        //         var_dump($queries);exit;
        //        print_r($data);
        return $data->toArray();
    }

    //根据订单ID返回数据
    public function  getByOrder($order_id){
        $data = $this->where('order_id',$order_id)->first();
        return $data ;
    }

    //重新结算某个订单;
    public function  reSelementOrder($order_id){


     }



}