You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
242 lines
6.8 KiB
PHTML
242 lines
6.8 KiB
PHTML
5 years ago
|
<?php
|
||
|
|
/**
|
||
|
|
* Created by PhpStorm.
|
||
|
|
* User: xmy 280564871@qq.com
|
||
|
|
* Date: 2017/5/2
|
||
|
|
* Time: 10:14
|
||
|
|
*/
|
||
|
|
|
||
|
|
namespace Open\Model;
|
||
|
|
|
||
|
|
class UserLoginRecordModel extends BaseModel
|
||
|
|
{
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 新增玩家
|
||
|
|
* @param string $map
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getAddPlayer($start, $end, $develop_id,$game_id="")
|
||
|
|
{
|
||
|
|
$map['g.developers'] = $develop_id;
|
||
|
|
$map['lpuid'] = 0;
|
||
|
|
empty($game_id) || $map['r.game_id'] = $game_id;
|
||
|
|
$sql = $this->alias("r")->field("r.user_id,r.game_id,min(r.login_time) as time")
|
||
|
|
->join("right join tab_game g on g.id = r.game_id ")
|
||
|
|
->group("r.game_id,r.user_id")
|
||
|
|
->where($map)
|
||
|
|
->select(false);
|
||
|
|
$sql = "select IFNULL(count(res.user_id),0) as num from ({$sql}) res
|
||
|
|
RIGHT JOIN sys_date_list d on d.time = FROM_UNIXTIME(res.time,'%Y-%m-%d')
|
||
|
|
WHERE d.time BETWEEN '{$start}' and '{$end}' GROUP BY d.time";
|
||
|
|
$data = M()->query($sql);
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 新增玩家
|
||
|
|
* @param integer $start 开始时间
|
||
|
|
* @param integer $end 结束时间
|
||
|
|
* @param integer $develop_id 开发者编号
|
||
|
|
* @param integer $game_id 游戏编号
|
||
|
|
* @author 鹿文学
|
||
|
|
*/
|
||
|
|
public function get_news_player($start, $end, $develop_id,$game_id="") {
|
||
|
|
$map['g.developers'] = $develop_id;
|
||
|
|
$map['lpuid'] = 0;
|
||
|
|
$map['r.login_time'] = array('gt',0);
|
||
|
|
$group = 'r.user_id';
|
||
|
|
if(is_numeric($game_id)){
|
||
|
|
$map['r.game_id'] = $game_id;
|
||
|
|
$group = 'r.game_id,r.user_id';
|
||
|
|
}
|
||
|
|
|
||
|
|
$sql = $this->alias('r')->field('r.user_id,r.game_id,min(r.login_time) as time')
|
||
|
|
->join('right join tab_game g on g.id = r.game_id ')
|
||
|
|
->group($group)
|
||
|
|
->where($map)
|
||
|
|
->select(false);
|
||
|
|
|
||
|
|
$data = $this->table('(' .$sql. ') as a')->field('IFNULL(count(a.user_id), 0) AS num')
|
||
|
|
->join('sys_date_list d on d.time = FROM_UNIXTIME(a.time,"%Y-%m-%d")','right')
|
||
|
|
->where(['d.time'=>array('between',[$start,$end])])->group('d.time')->select();
|
||
|
|
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 玩家
|
||
|
|
* @param string $map
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: cy 707670631@qq.com
|
||
|
|
*/
|
||
|
|
public function getPlayers($map=""){
|
||
|
|
$data = $this->alias("r")->field("r.*")
|
||
|
|
->join("left join tab_game g on g.id = r.game_id")
|
||
|
|
->group("user_id")
|
||
|
|
->where($map)
|
||
|
|
->select();
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
/**
|
||
|
|
* 查找玩家
|
||
|
|
* @param string $map
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: cy 707670631@qq.com
|
||
|
|
*/
|
||
|
|
public function findPlayer($map=""){
|
||
|
|
$data = $this->alias("r")->field("r.*")
|
||
|
|
->join("left join tab_game g on g.id = r.game_id")
|
||
|
|
->group("user_id")
|
||
|
|
->where($map)
|
||
|
|
->find();
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 活跃玩家
|
||
|
|
* @param string $map
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getActivePlayer($map = "", $develop_id,$game_id="")
|
||
|
|
{
|
||
|
|
empty($game_id) || $join = "and g.id = {$game_id}";
|
||
|
|
$sql = $this->alias("r")->field("r.user_id,d.time")
|
||
|
|
->join("right join tab_game g on g.id = r.game_id and g.developers = {$develop_id} {$join}")
|
||
|
|
->join("right join sys_date_list d on d.time = FROM_UNIXTIME(r.login_time,'%Y-%m-%d')")
|
||
|
|
->group("r.game_id,r.user_id,d.time")
|
||
|
|
->where($map)
|
||
|
|
->select(false);
|
||
|
|
$sql = "select IFNULL(count(res.user_id),0) as num ,res.time from ({$sql}) res GROUP BY res.time";
|
||
|
|
$data = M()->query($sql);
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
/**
|
||
|
|
* 获取老玩家
|
||
|
|
* @param $start
|
||
|
|
* @param $end
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getOldPlayer($start, $end, $develop_id,$game_id)
|
||
|
|
{
|
||
|
|
empty($game_id) || $map['game_id'] = $game_id;
|
||
|
|
$map['g.developers'] = $develop_id;
|
||
|
|
$start = strtotime($start);
|
||
|
|
$end = strtotime($end);
|
||
|
|
for ($time = $start; $time <= $end; $time += 86400) {
|
||
|
|
$data[] = $this->alias("r")->field("count(DISTINCT r.user_id) as num")
|
||
|
|
->join("left join tab_game g on g.id = r.game_id")
|
||
|
|
->where(['r.login_time' => ['lt', $time]])
|
||
|
|
->where($map)
|
||
|
|
->find();
|
||
|
|
}
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 获取留存人数
|
||
|
|
* @param $start
|
||
|
|
* @param $end
|
||
|
|
* @param $develop_id
|
||
|
|
* @return array
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getKeepPlayer($start,$end,$develop_id,$game_id,$day=['1']){
|
||
|
|
$start = strtotime($start);
|
||
|
|
$end = strtotime($end);
|
||
|
|
empty($game_id) || $map['r.game_id'] = $game_id;
|
||
|
|
for ($time = $start; $time <= $end; $time += 86400) {
|
||
|
|
$tomorrow = $time + 86399;
|
||
|
|
//新增用户
|
||
|
|
$add_sql = $this->alias("r")->field("r.user_id,r.game_id,min(r.login_time) as time")
|
||
|
|
->join("right join tab_game g on g.id = r.game_id and g.developers = {$develop_id}")
|
||
|
|
->where($map)
|
||
|
|
->group("r.game_id,r.user_id")
|
||
|
|
->having("time between {$time} and {$tomorrow}")
|
||
|
|
->select(false);
|
||
|
|
foreach ($day as $key => $value){
|
||
|
|
$data[$value.'RatentionRate'][] = $this->alias("r")->field("count(DISTINCT r.user_id) as num")
|
||
|
|
->join("inner join ({$add_sql}) res on res.user_id = r.user_id and res.game_id = r.game_id")
|
||
|
|
->where(['login_time'=>['between',[$time+$value*86400,$time+($value+1)*86400-1]]])
|
||
|
|
->find ();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 获取留存人数
|
||
|
|
* @param integer $start
|
||
|
|
* @param integer $end
|
||
|
|
* @param integer $develop_id
|
||
|
|
* @param integer $game_id
|
||
|
|
* @param array $day
|
||
|
|
* @return array
|
||
|
|
* author: 鹿文学
|
||
|
|
*/
|
||
|
|
public function get_keep_player($start,$end,$develop_id,$game_id='',$day=['1']) {
|
||
|
|
$map['lpuid'] = 0;
|
||
|
|
$map['r.login_time'] = array('gt',0);
|
||
|
|
$group = 'r.user_id';
|
||
|
|
if(is_numeric($game_id) && $game_id>0) {
|
||
|
|
$map['r.game_id'] = $game_id;
|
||
|
|
$group = 'r.game_id,r.user_id';
|
||
|
|
$game = ' and res.game_id = r.game_id ';
|
||
|
|
}
|
||
|
|
for ($time = $start; $time <= $end; $time += 86400) {
|
||
|
|
$tomorrow = $time + 86399;
|
||
|
|
//新增用户
|
||
|
|
$add_sql = $this->alias("r")->field("r.user_id,r.game_id,min(r.login_time) as time")
|
||
|
|
->join("right join tab_game g on g.id = r.game_id and g.developers = {$develop_id}")
|
||
|
|
->where($map)
|
||
|
|
->group($group)
|
||
|
|
->having("time between {$time} and {$tomorrow}")
|
||
|
|
->select(false);
|
||
|
|
foreach ($day as $key => $value){
|
||
|
|
$data[$value.'RatentionRate'][] = $this->alias("r")->field("count(DISTINCT r.user_id) as num")
|
||
|
|
->join("inner join ({$add_sql}) res on res.user_id = r.user_id {$game}")
|
||
|
|
->where(['login_time'=>['between',[$time+$value*86400,$time+($value+1)*86400-1]]])
|
||
|
|
->find ();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return $data;
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 累计玩家
|
||
|
|
* @param $map
|
||
|
|
* @return mixed
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getTotalUser($map){
|
||
|
|
$data = $this->field("count(DISTINCT user_id) as num")
|
||
|
|
->where($map)
|
||
|
|
->find();
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* 开发者 累计玩家
|
||
|
|
* @param $map
|
||
|
|
* @param $develop_id
|
||
|
|
* @return mixed
|
||
|
|
* author: xmy 280564871@qq.com
|
||
|
|
*/
|
||
|
|
public function getTotalUserJoinGame($map){
|
||
|
|
$data = $this->alias("r")->field("count(DISTINCT user_id) as num")
|
||
|
|
->join("right join tab_game g on g.id = r.game_id")
|
||
|
|
->where($map)
|
||
|
|
->find();
|
||
|
|
return $data;
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|