sports_admin/app/Models/Game_diceone.php

<?php
namespace App\Models;
use DB;

class Game_diceone extends BaseModel {

	protected $table = "game_diceone";
	public $timestamps = false;
	//获取注单信息
	function getBuyInfo($game, $list = 20, $fild = 'create_time', $order = 'desc', $where = '',$page=1) {
	    $frist=($page-1)*$list;
//        DB::connection()->enableQueryLog();
		if (!empty($where)&&count($where)>0) {
			$model = $this->where($where)
				->orderBy('create_time', $order);
		}else{
            $model = $this->orderBy('create_time', $order);
        }
		if(!empty($list)){
			$model=$model->paginate($list);
		}else{
			$model=$model->get();
		}

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

	//获取异常注意信息
	function getBtrecordBuyInfo($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','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();
	}

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

	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'))->groupBy('game_name');
			foreach ($having as $v) {
				$data = $data->havingRaw($v);
			}
			$data = $data->orderby('star_time','desc')->paginate($list);
		} 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'))->groupBy('game_name');
			foreach ($having as $v) {
				$data = $data->havingRaw($v);
			}
			$data = $data->orderby('star_time','desc')->paginate($list);
		} 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'))->groupBy('game_name')->orderby('star_time','desc')->paginate($list);
		}

		if (!isset($data) || !$data) {

		} else {
			$data = $data->toArray();
			$gamename = trans('report');
			foreach ($data['data'] as $k => $v) {
				$data['data'][$k]['name'] = $gamename[$v['game_name']];
				$data['data'][$k]['munBet'] = $v['munbet'];
			}
			return $data['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 where status != 4 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;

	}

	//获取回水列表
	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,max("account_identity") as account_identity'))->groupBy('account_name');

		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('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();
	}

	//获取用户回水详情列表
	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];
	}

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

	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]);
//        $data=$this->where('game_name',$game)->groupBy('account_identity')->sum('get_money')->sum('money')->count('id');
		$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]['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]['name'] = trans('report.' . $game);
			$array[$k]['account_name'] = $v->account_name;
		}
		$datas['data'] = $array;
		$datas['total'] = $count;
		return $datas;
	}
	//获取投注失败的订单
	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();
	}
	//获取全部注单信息
	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);
		}
		if(!empty($list)){
			$data=$data->paginate($list);
		}else{
			$data=$data->get();
		}
		if (!$data) {
			return -5030000222; //没有数据
		};
		return $data->toArray();
	}

	//获取用户打码量
	function getHitcode($where) {
		//DB::connection()->enableQueryLog();
		$data = $this->select(DB::raw('sum("money") as "hitcode_count",max("account_identity") as account_identity,max("account_name") as account_name'))->where($where)->first();
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -5030000402; //没有数据
		}
		return $data->toArray();
	}
	//反水状态
	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;
	}
	//总投注
	function totalBet($where) {
		if (count($where)>0) {
			return $this->where($where)->sum('money');
		} else {
			return $this->sum('money');
		}
	}
	//总赢取
	function totalGetMoney($where) {
		if (count($where)>0) {
            $res = $this->where($where)->sum('get_money');
		} else {
            $res = $this->sum('money');
		}
		return -$res;
	}

	//异常总投注
	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');
		}
	}
	//异常总赢取
	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;
	}
	//撤单
	function Cancel($where) {
		$res = $this->where($where)->where('game_status', 0)->update(['status' => 4]);
		if (!$res) {
			return -5051262522;
		}
		return 1;
	}
	//由id数组撤单
	function CancelByids($ids) {
		$res = $this->whereIn('id', $ids)->update(['status' => 4]);
		if (!$res) {
			return -5051262522;
		}
		return 1;
	}

	//统计每个游戏今日总投注金额
	function countTodayMoney() {
		$where = array(
			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'))->where($where)->groupBy('game_name')->get();

		if (!$data) {
			return -5000100102;
		}
		return $data->toArray();
	}
	
	
	//统计7日总投注金额趋势
	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 where (to_char(money_time, 'YYYY-MM-DD') >= '".$where."') group by money_date");

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

	//更新结算状态
    function updateSettle($account_arr,$start_time,$end_time){
        $model=$this->whereIn('account_name',$account_arr);
        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;
    }
    //管理员作废更新
    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;
    }
    //统计回水亏损金额用于计算
    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();
    }

    //代理下级用户投注金额，盈利统计统计
    function getChildMoneyTotal($child_arr,$timeArea){
        $data=$this->select(DB::raw('sum(money) as money,sum(get_money) as get_money,account_name'))->whereIn('account_name',$child_arr);
        $data->whereIn('status',array(1,2));
        if(!empty($timeArea)&&count($timeArea)>0){
            $data->where($timeArea);
        }
        $data->groupBy('account_name');
        $data=$data->get();
        if(!$data){
            return -8040410022;//没有统计数据
        }
        return $data->toArray();
    }
    	//一段时间内的提现人数
    	function countUser($timearea){
    	    $data=$this->select('account_name')->where('status', '<>', 4)->whereBetween('money_time', $timearea)->groupBy('account_name')->get();
    	    if(!$data){
    	        return 0;
            }
            $data=$data->toArray();
    	    return count($data);
        }
        //获取下级流水键代理id对流水金额
    function getChildSumMoney($agent_identity_arr,$time1,$time2){
//        $agent_identity_arr=implode('","',$agent_identity_arr);//转换成字符串
//        DB::con
//    	    $sql='SELECT "sum"(money) as money,agent_identity  FROM money_buy LEFT JOIN
// agent_child on agent_child.account_identity=money_buy.account_identity
//WHERE agent_identity in("'.$agent_identity_arr.'") ';
//    	    foreach ($agent_identity_arr as $v){
//                $sql.='"'.$v.'"';
//            }
//            $sql.=') ';

        $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);
    	    if(!empty($time1)){
//    	        $sql.='and money_time >= '.$time1;
                $data=$data->where('money_time','>=',$time1);
            }
            if(!empty($time2)){
//    	        $sql.=' and money_time <= '.$time2;
                $data=$data->where('money_time','<=',$time2);
            }
//            $sql.=' GROUP BY agent_identity';
            $data=$data->groupBy('agent_identity')->get();
//        $data = DB::select($sql);
        if (!$data) {
            return -5000100202;
        }
//        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;

    }
    //统计用户当前期投注总金额
    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');
    }
}