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

}