sports_admin/app/Models/Nagent_detailed.php

<?php

namespace App\Models;
use Illuminate\Support\Facades\Log;
use DB;

/**
 *
 */
class Nagent_detailed extends BaseModel {

    protected $table = 'nagent_detailed';
    public $timestamps = false;


    /**
     * 代理列表数据
     * [agent description]
     * @param  [type] $page [description]
     * @return [type]       [description]
     */
    function agent($list,$page,$where='') {
        $counttime = date('Y-W');
        $sql = "SELECT b.agent_identity,b.id, b.agent_user, b.register_time,(SELECT count(a.id) FROM nagent_detailed a WHERE a.parent_id = b.agent_identity) as sum_child,b.phone,b.email, b.state,b.money_total,b.zt_water,b.group_water,b.ns_ztwater,b.ns_money,b.settle_money,b.my_water,b.as_money,b.blacklist,b.invite,d.wmy_water,d.wzt_water,d.wgroup_water,d.sum_water,d.game_water FROM nagent_detailed b LEFT JOIN (SELECT c.agent_identity,c.agent_water as wmy_water,c.zt_water as wzt_water,c.group_water as wgroup_water,c.sum_water,c.game_water FROM nagent_countwater c WHERE count_time='" . $counttime . "') d ON d.agent_identity=b.agent_identity LEFT JOIN (SELECT count(a.id),a.parent_id FROM nagent_detailed a WHERE a.parent_id IS NOT NULL GROUP BY a.parent_id) e ON b.agent_identity=e.parent_id";
        $countsql = "SELECT count(b.id) FROM nagent_detailed b LEFT JOIN (SELECT count(a.id),a.parent_id FROM nagent_detailed a WHERE a.parent_id IS NOT NULL GROUP BY a.parent_id) e ON b.agent_identity=e.parent_id";
        if(!empty($where)){
            $sql .= ' ' . $where;
            $countsql .= ' ' . $where;
        }
        if (!empty($list)) {
            $offset = $list * ($page - 1);
            $sql .= ' ORDER BY b.register_time desc LIMIT ' . $list . ' OFFSET ' . $offset;
        }

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

        return $res;
    }


    /**
     * 代理列表数据
     * [agent description]
     * @param  [type] $page [description]
     * @return [type]       [description]
     */
    function nagentList($list,$page,$where='') {
        $counttime = date('Y-W');
        $sql = "SELECT b.agent_identity,b.id, b.agent_user, b.register_time,(SELECT count(a.id) FROM nagent_detailed a WHERE a.parent_id = b.agent_identity) as sum_child,b.phone,b.email, b.state,b.money_total,b.zt_water,b.group_water,b.ns_ztwater,b.ns_money,b.settle_money,b.my_water,b.as_money,b.blacklist,b.invite,d.wmy_water,d.wzt_water,d.wgroup_water,d.sum_water,d.game_water FROM nagent_detailed b LEFT JOIN (SELECT c.agent_identity,c.agent_water as wmy_water,c.zt_water as wzt_water,c.group_water as wgroup_water,c.sum_water,c.game_water FROM nagent_countwater c WHERE count_time='" . $counttime . "') d ON d.agent_identity=b.agent_identity INNER JOIN (SELECT count(a.id),a.parent_id FROM nagent_detailed a WHERE a.parent_id IS NOT NULL GROUP BY a.parent_id) e ON b.agent_identity=e.parent_id";
        $countsql = "SELECT count(b.id) FROM nagent_detailed b INNER JOIN (SELECT count(a.id),a.parent_id FROM nagent_detailed a WHERE a.parent_id IS NOT NULL GROUP BY a.parent_id) e ON b.agent_identity=e.parent_id";
        if(!empty($where)){
            $sql .= ' ' . $where;
            $countsql .= ' ' . $where;
        }
        if (!empty($list)) {
            $offset = $list * ($page - 1);
            $sql .= ' ORDER BY b.register_time desc LIMIT ' . $list . ' OFFSET ' . $offset;
        }

        //Log::info ($sql);

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

        return $res;
    }

    //导入用户信息到代理详情表
    protected function addData($list='', $page=''){
        $userinfo = $this->agentsInfo($list,$page);
        if(is_array($userinfo) && count($userinfo)>0){
            $res = $this->insert($userinfo);
            if($res<1){
                return -1;
            }
            return 1;
        }else{
            return $userinfo;
        }
    }

    //查询默认代理详情信息
    protected function agentsInfo($list='', $page='')
    {
        // DB::connection()->enableQueryLog();
        $agent_join = " LEFT JOIN agent_child ON account.identity=agent_child.account_identity";
        $agentId_join = " LEFT JOIN agent_child ON account.identity=agent_child.account_identity";

        $sql = "SELECT account.identity as agent_identity,parent_id,parent_path,account as agent_user, level as grade,open_invitation as invite  register_time, phone, email, (select count(id) FROM agent_child where agent_child.agent_identity=account.identity) as sum_child FROM account LEFT JOIN account_detailed ON account.identity=account_detailed.account_identity ORDER BY account.id asc";
        if (!empty($list)) {
            $offset = $list * ($page - 1);
            $sql .= ' LIMIT ' . $list . ' OFFSET ' . $offset;
        }

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

        if (!$data < 0) {
            return -2020052003; //没有用户会员
        }
        $data = json_encode ($data);
        $data = json_decode ($data, 1);
        return $data;
    }

    //查找代理id
    function findId($data){
        $datas = $this->select('id','agent_identity')->whereIn('agent_identity',$data)->get();
        if (!$datas < 0) {
            return -2021026003; //没有代理用户
        }
        return $datas->toArray();
    }

    /**
     *代理审核
     * [agency description]
     * @param  [type] $page [description]
     * @return [type]       [description]
     */
    function agency($list,$page,$where='') {
        if(!empty($where)&&is_array($where)){
            $data = $this->select('id','agent_user', 'start_time', 'phone', 'email', 'status')
            ->orderBy('start_time', 'desc')
            ->where('status', 0)
            ->where($where)
            ->paginate($list);
        }else{
            $data = $this->select('id','agent_user', 'start_time', 'phone', 'email', 'status')
            ->orderBy('start_time', 'desc')
            ->where('status', 0)
            ->paginate($list);
        }

        if (!$data < 0) {
            return -2021026003; //没有代理用户
        }
        return $data->toArray();
    }
    //代理审批
    function passOrNo($id,$status){
        if($status==1){
            $res=$this->where('id',$id)->update(['status'=>$status,'grade'=>1]);
        }else{
            $res=$this->where('id',$id)->delete();
        }
        if(!$res){
            return -8005013322;
        }
        return 1;
    }


    //代理信息获取
    protected function agentInfo($id){
        $data=$this->where('id',$id)->first();
        if(!$data){
            return -8005014322;
        }
        return $data->toArray();
    }
    //代理信息更新
    protected function updateInfo($data,$id){
        $res=$this->where('id',$id)->update($data);
        if(!$res){
            return -8005034322;
        }
        return 1;
    }
    //获取代理
    protected function getInfo($val,$num){
        $fild=$this->getFild($num);
        $data=$this->where($fild,$val)->first();
        if(!$data){
            return -8005014322;
        }
        return $data->toArray();
    }
    //获取字段
    function getFild($num){
        //
        $data=array(
            '1'=>'id',
            '2'=>'agent_user',
        );
        return $data[$num];
    }

    //代理统计非六合彩
    function totalSum($time,$where){
        DB::connection()->enableQueryLog();
        $sql="select agent_name,total_money,grade,total_money_profit from nagent_detailed f 
INNER JOIN(SELECT agent_name,sum(money) as total_money,SUM(get_money) as total_money_profit
FROM (
    SELECT SUM(money) as money,sum(get_money) as get_money,b.account_identity,min(c.agent_name) as agent_name FROM money_buy b 
RIGHT JOIN nagent_child c on c.account_identity = b.account_identity LEFT JOIN game_type x on x.table_name=b.game_name  and game_name != 'sixlottery'";
//        and  game_name != 'sixlottery'
        if(!empty($time)){
            $sql.=''.$time;
        }
    $sql.=" WHERE b.status !='4' and game_status>0 and x.agent_is_water<2 and game_status > 0 GROUP BY b.account_identity) as d GROUP BY d.agent_name) e  ON e.agent_name=f.agent_user";

        if(!empty($where)){
            $sql.=' '.$where;
        }
        $data=DB::select($sql);
        if (!$data) {
            return -8020052003; //没有用户会员
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        // $queries = DB::getQueryLog();
        // print_r($queries);
        return $data;
    }
    //代理统计六合彩
    function totalSumSix($time,$where){
        $sql="select agent_name,six_money,grade,six_money_profit from nagent_detailed f 
INNER JOIN(SELECT agent_name,sum(money) as six_money,SUM(get_money) as six_money_profit
FROM (
    SELECT SUM(money) as money,sum(get_money) as get_money,b.account_identity,min(c.agent_name) as agent_name FROM money_buy b 
RIGHT JOIN nagent_child c on c.account_identity=b.account_identity and game_name = 'sixlottery'";
        if(!empty($time)){
            $sql.=' '.$time;
        }
        $sql.=" WHERE status !='4' and game_status>0 GROUP BY b.account_identity) as d GROUP BY d.agent_name) e  ON e.agent_name=f.agent_user";
        if(!empty($where)){
            $sql.=' '.$where;
        }
        $data=DB::select($sql);
        if (!$data) {
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        return $data;
    }
    protected function addMoneyCopy($agent_identity,$money){
        return $this->addMoney($agent_identity,$money);
    }

    //代理加钱
    protected function addMoney($agent_identity,$money){
        $res=$this->where('agent_identity',$agent_identity)->increment('money_total',$money);
        if(!$res){
            return -8005466322;
        }
        return 1;
    }

    //代理团队提成统计
    protected function GroupSumMoney($time,$agent_identity=''){
        $sixmoney = $this->SixGroupSM($time);
        $sql="SELECT SUM(money) as money FROM (SELECT SUM(money) as money,b.account_identity FROM money_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity) a";

        $data=DB::select($sql);
        if (!$data) {
            if($sixmoney>0)return $sixmoney;
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        $money = current($data)['money'];
        $money = ($sixmoney>0)?($sixmoney+$money):$money;
        return $money;
    }

    //六合彩代理团队提成统计
    protected function SixGroupSM($time){
        $sql="SELECT SUM(money) as money FROM (SELECT SUM(money) as money,b.account_identity FROM sixmoney_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity) a";

        $data=DB::select($sql);
        if (!$data) {
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        return current($data)['money'];
    }

    //代理直推提成统计
    protected function StraightSumMoney($time,$agent_name=''){
        $sixdata = $this->SixSMoney($time);
        $sql="SELECT SUM(money) as money,b.account_identity FROM money_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity";

        $data=DB::select($sql);
        if (!$data) {
            if(is_array($sixdata))return $sixdata;
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        if($sixdata<0)return $data;
        $resdata = array();
        foreach ($data as $k => $v) {
            $resdata[$v['account_identity']] = isset($resdata[$v['account_identity']])?($resdata[$v['account_identity']]+$v['money']):$v['money'];
        }
        foreach ($sixdata as $k => $v) {
            $resdata[$v['account_identity']] = isset($resdata[$v['account_identity']])?($resdata[$v['account_identity']]+$v['money']):$v['money'];
        }
        $data = array();
        $i = 0;
        foreach ($resdata as $k => $v) {
            $data[$i]['money'] = $v;
            $data[$i]['account_identity'] = $k;
            $i++;
        }
        return $data;
    }

    //代理直推提成总额
    protected function StraightSumMoneys($time,$agent_name=''){
        $sixmoney = $this->SixSMoneys($time);
        $sql="SELECT SUM(money) as money FROM (SELECT SUM(money) as money,b.account_identity FROM money_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity) a";

        $data=DB::select($sql);
        if (!$data) {
            if($sixmoney>0)return $sixmoney;
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        $money = current($data)['money'];
        $money = ($sixmoney>0)?($money+$sixmoney):$money;
        return $money;
    }

    //六合彩代理直推提成统计
    protected function SixSMoney($time){
        $sql="SELECT SUM(money) as money,b.account_identity FROM sixmoney_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity";

        $data=DB::select($sql);
        if (!$data) {
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        return $data;
    }

    //六合彩代理直推提成总额
    protected function SixSMoneys($time){
        $sql="SELECT SUM(money) as money FROM (SELECT SUM(money) as money,b.account_identity FROM sixmoney_buy b RIGHT JOIN nagent_detailed c on c.agent_identity=b.account_identity and game_status <> 0 and status <> 4";
        if(!empty($time)){
            $sql.=$time;
        }

        $sql.=" GROUP BY b.account_identity) a";

        $data=DB::select($sql);
        if (!$data) {
            return -8020052003; //没有
        }
        $data = json_encode($data);
        $data = json_decode($data,1);
        return current($data)['money'];
    }

    //代理下级列表获取
    function getChild($identity,$where,$list=50){
        $data=$this->select('agent_user','agent_identity','register_time','zt_water','group_water','ns_ztwater','ns_money','phone','email','ratio','group_ratio','money_total','settle_money')
            ->where('parent_id',$identity);
        if(!empty($where) && count($where)>0){
            $data->where($where);
        }
        $data=$data ->paginate($list);

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

    //代理域名检测 2019-01-16 anton liu
    protected function checkDom($dmo,$id)
    {
        if($id){
            return $this
                ->where('id','!=',$id)
                ->where('domain_name','like','%'.$dmo.'%')
                ->first();
        }
        return $this
            ->where('domain_name','like','%'.$dmo.'%')
            ->first();
    }
}