crazy_god_API/api/background/Vip.php

<?php
/**
 * Vip统计相关
 */
defined( 'IN_WEB') or die( 'Include Error!');

include_once dirname(__FILE__) . "/Base.php";


class Vip extends  Base{
	
	//积分等级分布 & VIP开通人数
	public function lvDistribution($param){
		$start_time = microtime();
		$where = '';
		if($param['open'] == 1){
			$where .= " AND vip = 1";
		}else{
			$where .= " AND vip = 0";
		}
		$tb  = otable::VipLvConfig();
        $sql = "SELECT * FROM {$tb} WHERE 1";
        $levelConfig = oo::commonOprDb('config')->getAll($sql,MYSQLI_ASSOC);
        $userinfoTb = otable::userinfo(1);
        
        $out = [];
        //方法一 for循环查询
        // foreach ($levelConfig as $key=>$row){
        // 	$vipLv = $row['lv'];
        //     $sql = "SELECT count(*) as count FROM {$userinfoTb} WHERE vipLv = '{$vipLv}' {$where}";
        //     $ret = oo::commonOprDb('userinfo')->getOne($sql, MYSQLI_ASSOC);
        //     $out[$vipLv] = $ret['count'];
        // }
        
        //方法二 SELECT vipLv,count(*) FROM t_userinfo GROUP BY vipLv 一条语句查完
		//开通人数
        $sql = "SELECT vipLv,count(*) AS count FROM {$userinfoTb} WHERE vipLv > 0 {$where} GROUP BY vipLv";
        $ret = oo::commonOprDb('userinfo')->getAll($sql, MYSQLI_ASSOC);
        $arr = [];
        foreach($ret as $k => $v){
        	$arr[$v['vipLv']] = $v['count'];
        }
        foreach ($levelConfig as $key=>$row){
        	$vipLv = $row['lv'];
        	$out[$vipLv] = isset($arr[$vipLv])?$arr[$vipLv]:0;
        }
        return json_encode(['data'=>$out,'resultTime'=>microtime() - $start_time]);
	}
	
	//VIP玩家详情
	public function paySta($param){
		$limit = oo::functions()->uint($param['limit']);
        $page = oo::functions()->uint($param['page']);
        if(empty($limit)){
        	$limit = 10;
        }
        if(empty($page)){
        	$page = 1;
        }
        $start = ($page - 1) * $limit;
        $sort = oo::functions()->escape($param['sort']);
        $uid = oo::functions()->uint($param['uid']);
        if($sort == "time"){
        	$orderSql = "ORDER BY sl_pay_time DESC";
        }elseif($sort == "count"){
        	$orderSql = "ORDER BY count DESC";
        }else{
        	$orderSql = "ORDER BY sl_pay_time DESC";
        }
        
        //积分排序
        if($sort == 'point'){
        	//查询用户表
        	$where = '';
	        if($uid){
	        	$where = " AND uid = $uid";
	        }
        	$table = otable::userinfo(1);
        	$sql = "SELECT uid,vip,vipPoint,vipLv FROM {$table} WHERE vipLv > 0 {$where} ORDER BY vipPoint DESC LIMIT {$start}, {$limit}";
        	$ret = oo::commonOprDb('userinfo')->getAll($sql,MYSQLI_ASSOC);
        	$sql2 = "SELECT count(*) as count FROM {$table} WHERE vipLv > 0";
			$ret2 = oo::commonOprDb('userinfo')->getOne($sql2,MYSQLI_ASSOC);
			$out = [];
			foreach ($ret as $k => $v){
				$uidArr[] = $v['uid'];
			}
			if(empty($uidArr)){
				$uidArr[] = 100050;
			}
			$uidStr = implode(",",$uidArr);
			$table = otable::summarylist();
			$sql = "SELECT sl_uid,sl_gid,sl_pay_time,count(*) as count FROM {$table} WHERE sl_uid in($uidStr) AND sl_gid in(801,803) AND sl_status = 2 GROUP BY sl_uid ORDER BY sl_pay_time DESC";
			$summarylist = oo::commonOprDb('payment')->getAll($sql,MYSQLI_ASSOC);
			$list = [];
			foreach($summarylist as $k => $v){
				$list[$v['sl_uid']] = $v;
			}
			foreach ($ret as $k => $v){
				$uid = $v['uid'];
				if(isset($list[$uid])){
					$ret[$k]['sl_gid'] = $list[$uid]['sl_gid'];
					$ret[$k]['sl_pay_time'] = $list[$uid]['sl_pay_time'];
					$ret[$k]['count'] = $list[$uid]['count'];
				}else{
					$ret[$k]['sl_gid'] = 801;
					$ret[$k]['sl_pay_time'] = 0;
					$ret[$k]['count'] = 0;
				}
			}
			$out = $ret;
        }else{
        	$where = '';
	        if($uid){
	        	$where = " AND sl_uid = $uid";
	        }
	        
			$table = otable::summarylist();
			$sql = "SELECT sl_uid,sl_gid,sl_pay_time,count(*) as count FROM {$table} WHERE sl_gid in(801,803) AND sl_status = 2 {$where} GROUP BY sl_uid {$orderSql} LIMIT {$start}, {$limit}";
			
			$ret = oo::commonOprDb('payment')->getAll($sql,MYSQLI_ASSOC);
			$sql2 = "SELECT count(*) as count FROM (SELECT DISTINCT sl_uid FROM {$table} WHERE sl_gid in(801,803) AND sl_status = 2 {$where}) as a";
			$ret2 = oo::commonOprDb('payment')->getOne($sql2,MYSQLI_ASSOC);
			$out = [];
			foreach ($ret as $k => $v){
				$uid = $v['sl_uid'];
				$userinfo = oo::commonOprModel('member')->getUserInfo($uid);//玩家基本信息
				$data = [];
				$data['uid'] = $uid;
				$data['vipPoint'] = $userinfo['vipPoint'];
				$data['vip'] = $userinfo['vip'];
				$data['sl_pay_time'] = $v['sl_pay_time'];
				$data['sl_gid'] = $v['sl_gid'];
				$data['count'] = $v['count'];
				$out[] = $data;
			}
        }
        
		return json_encode(['data'=>$out,'total'=>$ret2['count']]);
	}
	
	//积分获得排行
	public function getRank($param){
		//$stime = strtotime("2020-03-05 20:00:00");//VIP积分获得规则代码上传时间
		$stime = oo::functions()->escape($param['stime']);
        $etime = oo::functions()->escape($param['etime']);
        $rate = oo::functions()->uint($param['rate']);
        if(empty($rate)){
        	$rate = 100;
        }
        $dayList = $this->getDays($stime, $etime);
        $data = [];
		
		$table = otable::summarylist();
		$out = [];
        foreach($dayList as $date) {
        	//查询每个日期总积分
        	$sDay = strtotime($date." 00:00:00");
            $eDay = strtotime($date." 23:59:59");
        	$sql = "SELECT SUM(sl_pusd) as total FROM {$table} WHERE sl_status = 2 AND sl_pay_time > {$sDay} AND sl_pay_time < {$eDay}";
        	$ret = oo::commonOprDb('payment')->getOne($sql,MYSQLI_ASSOC);
        	$out[$date] = intval($ret['total']) * $rate;
        }
        $data['day'] = $out;
        //查询日期范围不同商品积分获取数量
        $sql = "SELECT sl_gid,sum(sl_pusd) AS total,count(*) AS count FROM {$table} WHERE sl_status = 2 AND sl_pay_time > {$stime} AND sl_pay_time < {$etime} GROUP BY sl_gid";
        $ret = oo::commonOprDb('payment')->getAll($sql,MYSQLI_ASSOC);
        $data['sumDay'] = $ret;
        
        //商品数据
        $gTable = otable::goodsCrazy();
        $sql = " SELECT gid,gpid,gname,gprice FROM {$gTable}";
	    $goodsCrazy = oo::commonOprDb('common')->getAll($sql, 1);
	    $data['goods'] = $goodsCrazy;
	    
        return json_encode(['data'=>$data]);
	}
	
	//每日VIP开通人数统计
	public function todayVipOpenSta($param){
		//每日新增开通人数
		/*
		$date = $param['date'];
        $table = otable::staData();
        $type = 'todayVipOpenSta';
        //当天数据
        $dateNew = date("Ymd",strtotime($date));
        $sql = "SELECT * FROM {$table} WHERE date IN ({$dateNew}) AND type = '{$type}'";
        $retNew = oo::commonOprDb('statistics')->getOne($sql,MYSQLI_ASSOC);
        
        $retNew['data'] = json_decode($retNew['data'],true);
		
		//前一天数据
		$dateLast = date("Ymd",strtotime($date)-86400);
		
        $sql = "SELECT * FROM {$table} WHERE date IN ({$dateLast}) AND type = '{$type}'";
        $retLast = oo::commonOprDb('statistics')->getOne($sql,MYSQLI_ASSOC);
        if(!empty($retLast)){
        	$retLast['data'] = json_decode($retLast['data'],true);
        }
        $data = array();
        foreach($retNew['data']['open'] as $k => $v){
        	$data['open'][$k] = $v - (isset($retLast['data']['open'][$k])?$retLast['data']['open'][$k]:0);
        	$data['no_open'][$k] = $retNew['data']['no_open'][$k] - (isset($retLast['data']['no_open'][$k])?$retLast['data']['no_open'][$k]:0);
        }
		
        return json_encode(['data'=>$data]);
        */
        
        //每日付费开通VIP人数
        $date = $param['date'];
        $sidList = json_decode($param['sidList'], 1);
        if(empty($date)){
        	$date = date("Y-m-d",time());
        }
        
        if(empty($sidList)){
        	$sidList = [999];
        }
        
        $where = '';
        if(!in_array(999,$sidList)){
        	$where .= " AND sl_sid in ( ". implode(',', $sidList)." )";
        }
        
        $stime = strtotime($date." 00:00:00");
        $etime = strtotime($date." 23:59:59");
            
        $table = otable::summarylist();
        
        $sql = "SELECT sl_uid,sl_gid,sl_pay_time,count(*) as count FROM {$table} WHERE sl_pay_time > {$stime} AND sl_pay_time < {$etime} AND sl_gid in(801,803) AND sl_status = 2 {$where} GROUP BY sl_uid ORDER BY sl_pay_time DESC";
		$summarylist = oo::commonOprDb('payment')->getAll($sql,MYSQLI_ASSOC);
		
		$data = [];
		foreach($summarylist as $k => $v){
			$uid = $v['sl_uid'];
			$userinfo = oo::commonOprModel('member')->getUserInfo($uid);//玩家基本信息
			if(!isset($data[$userinfo['vipLv']])){
				$data[$userinfo['vipLv']] = 0;
			}
			$data[$userinfo['vipLv']]++;
		}
		
		return json_encode(['data'=>$data]);
	}

	public function openVip($param){
	    if(empty($param['uid']) || empty($param['day']))
            return json_encode(['code'=>-1,'msg'=>'非法操作']);
        $uid = oo::functions()->uint($param['uid']);
        $day = oo::functions()->uint($param['day']);
        if(empty($uid) || empty($day))
            return json_encode(['code'=>-1,'msg'=>'非法操作']);
        $res = oo::commonOprModel('member')->updateUserInfo($uid, ['vip_expire' =>time() + $day * 86400,"vip"=>1]);
        return json_encode(['code'=>$res,'msg'=>'操作成功']);
    }
}