sports_admin/app/Models/Money_details.php

<?php
namespace App\Models;
use Illuminate\Support\Facades\DB;

class Money_details extends BaseModel {
	protected $table = "money_details";
	public $timestamps = false;

	//获取存取反水列表
	function getPartlist($value = '', $type = 1, $page = 10, $sort = 1, $ads = 'desc') {
//       DB::connection()->enableQueryLog();
		$key = $this->getFeild($type);
		$sort = is_integer($sort) ? $this->getFeild($sort) : $sort;

		$data = $this->select('money_details.id', 'money_details.trade_id', 'money_details.account_name', 'money_details.account_identity', 'money_details.money', 'money_details.money_time', 'money_details.money_type', 'money_details.money_cash', 'money_details.trade_type', 'money_details.trade_desc', 'money_details.reason', 'money_details.sysetem_user','money_details.status','money_recharge.remark')->leftJoin('money_recharge', 'money_recharge.order_id', '=', 'money_details.trade_id')->whereIn('trade_type', [5, 6, 7, 8, 9, 11, 12, 13, 14, 15,16,19,22]);
		if (is_array($value)) {
			$data = $data->where($value);
		} else {
			$data = $data->where($key, $value);
		}

		$data = $data->orderby($sort, $ads)->paginate($page);
//        $queries = DB::getQueryLog();

//        print_r($queries);
		if (!$data) {
			return -3021000102; //没有列表数据
		}
		return $data->toArray();
	}

	//获取资金流水列表
	function getMoneylist($value = '', $type = 1, $page = 10, $sort = 6, $ads = 'desc') {
		$key = $this->getFeild($type);
		$sort = is_integer($sort) ? $this->getFeild($sort) : $sort;
		//DB::connection()->enableQueryLog();
		if (is_array($value)) {
			$data = $this->where($value);
		} else {
			$data = $this->where($key, $value);
		}
		$data = $data->select('money_details.id', 'money_details.trade_id', 'money_details.account_name', 'money_details.account_identity', 'money_details.money', 'money_details.money_time', 'money_details.money_type', 'money_details.money_cash', 'money_details.trade_type', 'money_details.trade_desc', 'money_details.reason', 'money_details.sysetem_user','money_details.status','money_recharge.remark')->leftJoin('money_recharge', 'money_recharge.order_id', '=', 'money_details.trade_id')->orderby($sort, $ads)->orderby('money_details.id',$ads)->paginate($page);
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -3021000102; //没有列表数据
		}
		return $data->toArray();
	}

	//用户提现笔数区间筛选
	function getTakenumArea($where = '', $type = 1, $list = 10, $sort = 6, $ads = 'desc', $page = 1, $having = '', $whereIn = '') {
		//DB::connection()->enableQueryLog();
		$data = $this->select(DB::raw('max(account.id) as id, max(account.identity) as identity, max(account) as account, max(grade) as grade, max(cash) as cash, max(name) as name, max(register_time) as register_time, max(register_ip) as register_ip, max(last_ip) as last_ip, max(phone) as phone, max(email) as email, max(statuss) as statuss,max(last_time) as last_time,count("trade_id") as betnum'))->join('account_detailed', 'money_details.account_identity', '=', 'account_detailed.account_identity')->join('account', 'money_details.account_identity', 'account.identity')->groupBy('money_details.account_identity')->whereIn('trade_type', $whereIn)->where('money_details.status', 1);

		if (!empty($where) && is_array($where) && !empty($having) && is_array($having)) {
			$data = $data->where($where);
			foreach ($having as $v) {
				$data = $data->havingRaw($v);
			}
		} elseif (!empty($having) && is_array($having)) {
			foreach ($having as $v) {
				$data = $data->havingRaw($v);
			}
		} elseif (!empty($where) && is_array($where)) {
			$data = $data->where($where);
		}

		$data = $data->paginate($list);
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -3020100402; //没有订单统计信息
		}
		$data = $data->toArray();

		return $data;
	}

	//统计会员财务日志
	function getFundlog($value = '', $type = 1, $limit = 10, $sort = 6, $ads = 'desc',$page=1) {
		$key = $this->getFeild($type);
		$sort = is_integer($sort) ? $this->getFeild($sort) : $sort;
		//DB::connection()->enableQueryLog();
		$value .= empty($value)?' where ':' and ';
		$value .= 'status=1 ';
		$offset = ($page-1)*$limit;
		$sql = 'SELECT main.account_name,SUM(CASE WHEN trade_type=14 THEN money ELSE 0 END) as sendmoney,
		SUM(CASE WHEN trade_type=6 OR trade_type=11 THEN money ELSE 0 END) as savemoney,
		SUM(CASE WHEN trade_type=5 OR trade_type=9 THEN money ELSE 0 END) as getmoney,
		SUM(CASE WHEN trade_type=12 AND money_type=1 THEN money ELSE 0 END) as adminadd,
		SUM(CASE WHEN trade_type=12 AND trade_type=2 THEN money ELSE 0 END) as adminpull,
		SUM(CASE WHEN trade_type=13 THEN money ELSE 0 END) as recharge_send,
		SUM(CASE WHEN trade_type=7 OR trade_type=8 THEN money ELSE 0 END) as back_return,
		(SUM(CASE WHEN trade_type=14 OR trade_type=6 OR trade_type=11 THEN money ELSE 0 END)-
		SUM(CASE WHEN trade_type=5 OR trade_type=9 THEN money ELSE 0 END)) as winmoney 
		from money_details as main '.$value.' GROUP BY main.account_name LIMIT '.$limit.' OFFSET '.$offset;
		$countsql = 'SELECT COUNT(account_name) from (SELECT account_name from money_details '.$value.'  GROUP BY account_name) as a';
		$data = DB::select($sql);
		$count = DB::select($countsql);
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -3021000502; //没有列表数据
		}
		$data = json_encode($data);
		$data = json_decode($data,1);
		$resdata['data'] = $data;
		$resdata['total'] = $count[0]->count;
		return $resdata;

		/*$data = $this->select(DB::raw('account_name,MAX("account_identity") as account_identity'))->groupBy('account_name');

		if (is_array($value)) {
			$data = $data->where($value);
		} else if (!empty($value)) {
			$data = $data->where($key, $value);
		}
		if (!empty($whereIn) && is_array($whereIn)) {
			foreach ($whereIn as $k => $v) {
				$data = $data->whereIn($v[0], $v[1]);
			}
		}
		if (!empty($whereNotin) && is_array($whereNotin)) {
			foreach ($whereNotin as $k => $v) {
				$data = $data->whereNotIn($v[0], $v[1]);
			}
		}
		$data = $data->paginate($page);
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -3021000102; //没有列表数据
		}
		return $data->toArray();*/
	}

	//分类统计会员财务
	function getPartlog($value = '', $type = 1) {
		$key = $this->getFeild($type);
		$data = $this->select(DB::raw('trade_type,SUM("money") as summoney,money_type'))->groupBy('trade_type')->groupBy('money_type');

		if (is_array($value)) {
			$data = $data->where($value);
		} else if (!empty($value)) {
			$data = $data->where($key, $value);
		}

		$data = $data->get();
		//$queries = DB::getQueryLog();
		//print_r($queries);
		if (!$data) {
			return -3021000102; //没有列表数据
		}
		return $data->toArray();
	}
    //分类统计会员财务改
	function getPatlog($where){
        $data = $this->select(DB::raw('trade_type,sum("money") as summoney,money_type,account_name'))
            ->where($where)
            ->groupBy('trade_type')
            ->groupBy('money_type')
            ->groupBy('account_name')
            ->get();
        if (!$data) {
            return -3021000102; //没有列表数据
        }
        return $data->toArray();
    }

	//获取账务订单详情
	function getOrderDetails($value, $type = 1, $jointable = '', $columnn = '', $columnw = '') {
		$key = $this->getFeild($type);
		if (empty($jointable)) {
			$data = $this->where($key, $value)->first();
		} elseif ($jointable == 'account_bank') {
			$data = $this->select('money_details.account_name as account', 'trade_id', 'bank_name', 'account_bank.account_name', 'bank_number', 'bank_address', 'money_time', 'money', 'money_details.status', 'trade_type', 'reason')->where($key, $value)->join($jointable, $this->table . '.' . $columnn, $jointable . '.' . $columnw)->first();
		} else {
			$data = $this->where($key, $value)->join($jointable, $this->table . '.' . $columnn, $jointable . '.' . $columnw)->first();
		}

		if (!$data) {
			return -3021000402; //没有订单信息
		}
		return $data->toArray();
	}
	//获取游戏倍率
	function getOddsBy($game_code) {
		$data = $this->select('odds')->where('game_code', $game_code)->where('type', 2)->where('isparent_node', 1)->get();
		$data = $data->toArray();
		if (!$data) {
			return -8010011001; //没有倍率
		}
		$redata = array();
		//组合游戏项的倍率
		foreach ($data as $k => $v) {
			$redata = array_merge($redata, json_decode($v['odds'], 1));
		}
		return $redata;
	}

	//获取号码信息
	function getInfoByCode($code) {
		$data = $this->where('parent', $code)->get();
		if (!$data) {
			return -8020000501; //没有游戏玩法的号码
		}
		return $data->toArray();
	}
	//字段对应值
	private function getFeild($num) {
		$data = array(
			'1' => 'id',
			'2' => 'info_identity',
			'3' => 'trade_id',
			'4' => 'account_name',
			'5' => 'account_identity',
			'6' => 'money_time',
			'7' => 'money_type',
			'8' => 'trade_type',
			'9' => 'sysetem_user',
			'10' => 'status',
		);
		return $data[$num];
	}

	function updateInfo($data,$value,$field=1){
		$key = $this->getFeild($field);
		$res = $this->where($key,$value)->update($data);
		if($res<0){
			return -3021010102;	//更新失败
		}
		return 1;
	}

	function money($page, $identity, $status) {
		$data = $this->select('account_identity', 'money_type', 'money_time', 'trade_id', 'trade_type', 'money', 'money_cash', 'trade_desc', 'status')
			->orderBy('money_time', 'desc')
			->where('account_identity', $identity)
			->limit($page)
			->get();
		foreach ($data as $k => $v) {
			if ($v['money_type'] == 1 && $v['status'] == $status) {
				$data[$k]['old_money'] = sprintf("%.1f", $v['money_cash'] - $v['money']);
			} elseif ($v->money_type == 2 && $v['status'] == $status) {
				$data[$k]['old_money'] = sprintf("%.1f", $v['money_cash'] + $v['money']);
			}
		}
		if (!$data) {
			return -2020062003; //用户没有数据
		}
		return $data->toArray();
	}
	//插入数据
	function insertData($data) {
		if ($this->checkTrade_id($data['trade_id'])) {
			return -3010026022; //订单号已存在。
		}
		$data['info_identity'] = UUID();
		$res = $this->insert($data);
		if (!$res) {
			return -2020072022; //插入资金详情失败
		}
		return 1;

	}
	//获取用户的流水记录
	function getUserWater($account_id, $list = 10, $where = '') {
		$data = $this->where('account_identity', $account_id);
		if (!empty($where) && is_array($where)) {
			$data = $data->where($where);
		}
		$data = $data->orderBy('money_time', 'desc')->paginate($list);
		if (!$data) {
			return -8020010522; //没有数据
		}
		$datas = $data->toArray();
		if (is_array($datas['data']) && count($datas['data'])) {
			foreach ($datas['data'] as $k => $v) {
				if ($v['money_type'] == 1) {
					$datas['data'][$k]['old_money'] = $v['money_cash'] - $v['money'];
				} else {
					$datas['data'][$k]['old_money'] = $v['money_cash'] + $v['money'];
				}
			}
		}
		return $datas;
	}
	//检测交易订单号
	function checkTrade_id($order_id) {
		$res = $this->where('trade_id', $order_id)->first();
		if ($res) {
			return true;
		}
		return false;
	}
	//统计总金额
	function sumMoney($where) {
		$res = $this->where($where)->sum('money');
		return $res;
	}
	//获取存取反水总金额含活动赠送
	function getTotal($time) {
		$array = array(5, 6, 7, 8, 9, 11, 12, 13, 14);
		$res = $this->where($time)->whereIn('trade_type', $array)->sum('money');
		return $res;
	}
	//入款总额汇总
	function getIntoTotal($where) {
		$array = array(6, 11, 14);
		$res = $this->where($where)->whereIn('trade_type', $array)->sum('money');
		return $res;
	}
	//统计各项入款金额
	function sumIntoMoney($where) {
		$res = $this->where($where)->sum('money');
		return $res;
	}
	//测试统计
	function totalTypeMoney($where){
	    $type=array(5,6,7,8,9,12,11,13,14,16,19,10,22);
	    $data=$this->select(DB::raw('sum(money) as money,trade_type,money_type'))
            ->whereIn('trade_type',$type)
            ->where($where)
            ->groupBy('trade_type')
            ->groupBy('money_type')
            ->get();
	    return $data->toArray();
    }

	function getActiveMoney($where, $type) {
		$res = $this->where('money_type', $type)->where($where)->where('trade_type', 12)->sum('money');
		return $res;
	}

	//汇款金额汇总统计
	function sendMoneyCount($where = '') {
		$data = array();
		$data['all_money'] = $this->whereIn('trade_type', [13, 14])->sum('money');
		$data['success_money'] = $this->whereIn('trade_type', [13])->sum('money');
		$data['give_money'] = $this->whereIn('trade_type', [13, 14])->where('status', 1)->sum('money');
		$data['fail_money'] = $this->whereIn('trade_type', [13, 14])->where('status', 2)->sum('money');
		if (!empty($where) && is_array($where)) {
			$data['all_money'] = $this->where($where)->whereIn('trade_type', [13, 14])->sum('money');
			$data['success_money'] = $this->where($where)->whereIn('trade_type', [13])->sum('money');
			$data['give_money'] = $this->where($where)->whereIn('trade_type', [13, 14])->where('status', 1)->sum('money');
			$data['fail_money'] = $this->where($where)->whereIn('trade_type', [13, 14])->where('status', 2)->sum('money');
		}

		return $data;
	}
	//反水插入
	function returnData($data) {
		$res = $this->insert($data);
		if (!$res) {
			return -5020034002; //反水失败
		}
		return 1;
	}

	function getBet() {
		$time2 = date("Y-m-d H:i:s", time() - 60 * 60 * 24 * 7);
		$type=array(5,6,11,14);
		//提现
		$rech_data= $this->select(DB::Raw('date(money_time)'), DB::Raw('sum(money) as value'),'trade_type')->where('money_time', '>=', $time2)
            ->where('status',1)
            ->whereIn('trade_type',$type)
            ->orderBy(DB::Raw('date(money_time)'), 'asc')->groupBy(DB::Raw('date(money_time)'))->groupBy('trade_type')->get();
		if (!$rech_data) {
			return -3021010102; //没有列表数据
		}
		return $rech_data->toArray();
		//判断数据是否存在
		/*$rech_data['money_with'] = $rech_data['money_with']->toArray();
		$rech_data['money_recharge'] = $this->join('account', 'account.identity', '=', 'money_details.account_identity')->select(DB::Raw('date(money_time)'), DB::Raw('sum(money) as value'))->whereBetween('money_time', $timearea)->orderBy(DB::Raw('date(money_time)'), 'asc')->groupBy(DB::Raw('date(money_time)'))->where('trade_type', 6)->get();

		$rech_data['money_recharge'] = $rech_data['money_recharge']->toArray();
		$rech_data['money_remittance'] = $this->join('account', 'account.identity', '=', 'money_details.account_identity')->select(DB::Raw('date(money_time)'), DB::Raw('sum(money) as value'))->whereBetween('money_time', $timearea)->orderBy(DB::Raw('date(money_time)'), 'asc')->groupBy(DB::Raw('date(money_time)'))->where('trade_type', 11)->get();
		$rech_data['money_remittance'] = $rech_data['money_remittance']->toArray();
		$rech_data['money_trans'] = $this->join('account', 'account.identity', '=', 'money_details.account_identity')->select(DB::Raw('date(money_time)'), DB::Raw('sum(money) as value'))->whereBetween('money_time', $timearea)->orderBy(DB::Raw('date(money_time)'), 'asc')->groupBy(DB::Raw('date(money_time)'))->where('trade_type', 14)->get();
		$rech_data['money_trans'] = $rech_data['money_trans']->toArray();
		return $rech_data;*/
	}
	function countUser($timearea, $array) {
		$data = $this->select('account_name')->whereBetween('money_time', $timearea)->whereIn('trade_type', $array)->groupBy('account_name')->get();
		if (!$data) {
			return 0;
		}
		$data = $data->toArray();
		return count($data);
	}
    //下级会员资金列表
	function getChildMoneyList($childs,$list){
        $data=$this->whereIn('account_name',$childs)->orderBy('money_time','desc')->paginate($list);
        if (!$data) {
            return -3021000102; //没有列表数据
        }

        return $data->toArray();
    }

	//获取用户存取款笔数
	function getUserMoneynum($aid){
        $rechargenum=$this->whereIn('trade_type',array(6,12,11,14))->where('account_identity',$aid)->count();
		$takenum=$this->whereIn('trade_type',array(5))->where('account_identity',$aid)->count();
        $data = array(
			'recharge_num' => !$rechargenum?0:$rechargenum,
			'take_num' => !$takenum?0:$takenum,
		);
        return $data;
    }

    //批量插入数据
    function insertAllData($data) {
        $res = $this->insert($data);
        if (!$res) {
            return -2020072022; //插入资金详情失败
        }
        return 1;

    }
    //根据用户名统计该用户提款，充值成功次数
    function countUserNum($account_name){
	    $data=$this->select(DB::raw('count(id) as num,trade_type'))->where('status',1)->where('account_name',$account_name)->whereIn('trade_type',array(5,6,11,14))->groupBy('trade_type')->get();
	    if(!$data){
	        return array();
	    }
	    return $data->toArray();
    }
    //获取用户充值总金额
    function sumRecharge($account_name){
       return $this->where('account_name',$account_name)->where('status',1)->whereIn('trade_type',array(6,11,14))->sum('money');
    }

    protected 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;
    }

    /** 获取一种类型得数据
     * @param $type 交易类型
     * @param $limit 条数
     * @param $otherWhere 其他条件
     */
    protected function getTypeData($type,$limit,$otherwhere=array()){
        $data=$this->where('trade_type',$type);
        if(is_array($otherwhere)&&count($otherwhere)>0){
            $data=$data->where($otherwhere);
        }
        $data=$data->orderBy('id','asc');
        $data=$data->paginate($limit);
        return $data->toArray();
    }

    protected function getTypesData($typearray=array(),$limit){
        $data=$this->whereIn('trade_type',$typearray)->paginate($limit);
        return $data->toArray();
    }
}