123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256 |
- <?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'=>'操作成功']);
- }
- }
|