<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Mgame_details extends CI_Model
{
    protected $_table = 'game_details';

    public function __construct()
    {
        parent::__construct();
        date_default_timezone_set("Asia/Ho_Chi_Minh");
    }

    public function add($dataInsert)
    {
        $this->db->insert($this->_table, $dataInsert);
        $insert_id = $this->db->insert_id();
        return $insert_id;
    }

    public function update($id, $dataUpdate)
    {
        try {
            $this->db->where('id', $id);
            $this->db->update($this->_table, $dataUpdate);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Lấy kết quả theo ngày để hiển thị bảng kết quả
     * @return mixed
     */
    public function getResultByDate()
    {
        $this->db->select(
            'DATE(gd.created_at) as created_at,
            gd.user_id,
            u.fullname as fullname,
            SUM(gd.point) AS total_point,
            SUM(gd.time) AS total_time'
        );
        $this->db->from($this->_table. ' gd');
        $this->db->join('user u', 'u.id = gd.user_id');
        $this->db->group_by(array('DATE(gd.created_at)', 'gd.user_id'));
        $this->db->order_by('DATE(gd.created_at) ASC, total_point DESC, total_time` ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    /**
     * Lấy top 10 theo từng ngày chơi
     * @param null $date
     * @return mixed
     */
    public function getTopByDate($limit=10, $offset=0, $date=null, $name=null)
    {
        $this->db->select(
            'DATE(gd.created_at) as created_at,
            gd.user_id,
            u.fullname as fullname,
            u.username as username,
            u.email as email,
            u.mobile as mobile,
            u.avatar as avatar,
            SUM(gd.point) AS total_point,
            SUM(gd.time) AS total_time'
        );
        $this->db->from($this->_table. ' gd');
        $this->db->join('user u', 'u.id = gd.user_id');
        if($date != null){
            $this->db->where('DATE(gd.created_at)', $date);
        }else{
            $this->db->where('DATE(gd.created_at)', date('Y-m-d'));
        }
        if($name != null){
            $this->db->like('u.fullname', $name, 'both');
            $this->db->or_like('u.username', $name, 'both');
            $this->db->or_like('u.address', $name, 'both');
            $this->db->or_like('u.email', $name, 'both');
            $this->db->or_like('u.mobile', $name, 'both');
            $this->db->or_like('u.reward_code', $name, 'both');
        }
        $this->db->group_by(array('DATE(gd.created_at)', 'gd.user_id'));
        $this->db->order_by('DATE(gd.created_at) ASC, total_point DESC, total_time` ASC');
        $this->db->limit($limit, $offset);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getTopByDateInOneGame($limit=10, $offset=0, $date=null, $name=null)
    {
        /*SELECT MAX(gd.`point`) AS max_point, u.`fullname`
        FROM `game_details` gd
        LEFT JOIN USER u ON u.`id` = gd.`user_id`
        WHERE DATE(gd.`created_at`) = '2017-02-23'
        GROUP BY gd.`user_id`
        ORDER BY max_point DESC
        LIMIT 10;*/
        $this->db->select(
            'MAX(gd.point) as max_point, gd.time, DATE(gd.created_at) as created_at,
            gd.user_id,
            u.fullname as fullname,
            u.username as username,
            u.email as email,
            u.mobile as mobile,
            u.avatar as avatar,
            u.reward_code as reward_code,
            SUM(gd.point) AS total_point,
            SUM(gd.time) AS total_time'
        );
        $this->db->from($this->_table. ' gd');
        $this->db->join('user u', 'u.id = gd.user_id');
        if($date != null){
            $this->db->where('DATE(gd.created_at)', $date);
        }else{
            $this->db->where('DATE(gd.created_at)', date('Y-m-d'));
        }
        if($name != null){
            $this->db->like('u.fullname', $name, 'both');
            $this->db->or_like('u.username', $name, 'both');
            $this->db->or_like('u.address', $name, 'both');
            $this->db->or_like('u.email', $name, 'both');
            $this->db->or_like('u.mobile', $name, 'both');
            $this->db->or_like('u.reward_code', $name, 'both');
        }
        $this->db->group_by(array('gd.user_id'));
        $this->db->order_by('max_point DESC');
        $this->db->limit($limit, $offset);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getTopWeek($date1=null, $date7=null)
    {
        /*SELECT MAX(gd.`point`) AS max_point, u.`fullname`
        FROM `game_details` gd
        LEFT JOIN USER u ON u.`id` = gd.`user_id`
        WHERE DATE(gd.`created_at`) = '2017-02-23'
        GROUP BY gd.`user_id`
        ORDER BY max_point DESC
        LIMIT 10;*/
        $this->db->select(
            'MAX(gd.point) as max_point, gd.time, DATE(gd.created_at) as created_at,
            gd.user_id,
            u.fullname as fullname,
            u.username as username,
            u.email as email,
            u.mobile as mobile,
            u.avatar as avatar,
            u.reward_code as reward_code,
            SUM(gd.point) AS total_point,
            SUM(gd.time) AS total_time'
        );
        $this->db->from($this->_table. ' gd');
        $this->db->join('user u', 'u.id = gd.user_id');
        if($date1 != null && $date7 != null){
            $this->db->where('DATE(gd.created_at) >=', $date1);
            $this->db->where('DATE(gd.created_at) <=', $date7);
        }
        $this->db->group_by(array('gd.user_id'));
        $this->db->order_by('max_point DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getByUserId($user_id)
    {
        $this->db->select($this->_table . '.*');
        $this->db->from($this->_table);
        $this->db->where($this->_table . '.user_id', $user_id);
        $this->db->order_by($this->_table . '.created_at', 'desc');
        $this->db->limit(1);
        $query = $this->db->get();
        return $query->result_array();
    }

}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */