<?php

defined('BASEPATH') or exit('No direct script access allowed');

require_once 'Da_general.php';

class M_general extends Da_general
{

    public function __construct()
    {
        parent::__construct();
    }

    public function get_num_project($year)
    {
        $sql = "SELECT count(project_id) AS cluster, team, project, all_project, by_year FROM project,
            (SELECT count(project_id) AS team FROM project
                WHERE is_delete = 0 and team_id != 0 and YEAR(date) = $year) AS team, 
            (SELECT count(project_id) AS project FROM project
		        WHERE is_delete = 0 and team_id = 0 AND cluster_id = 0 and YEAR(date) = $year) AS project,
            (SELECT count(project_id) AS all_project FROM project
                WHERE is_delete = 0) AS all_project,
            (SELECT count(project_id) as by_year FROM project
		        Where is_delete = 0 and YEAR(date) = $year) as by_year
		WHERE is_delete = 0 and cluster_id != 0 and YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_2($year)
    {
        $sql = "SELECT *,project.name AS pj_name,cluster.name AS ct_name FROM {$this->db_name}.project
        JOIN cluster ON project.cluster_id = cluster.cluster_id
		WHERE is_delete = 0 AND project.cluster_id != 0 AND YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_year()
    {
        $sql = "SELECT YEAR(date) AS year FROM {$this->db_name}.project
        WHERE is_delete = 0
        GROUP BY YEAR(date)
        ORDER BY year DESC";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_2_year()
    {
        $sql = "SELECT YEAR(date) AS year FROM {$this->db_name}.project
        WHERE is_delete = 0 AND cluster_id != 0
        GROUP BY YEAR(date)
        ORDER BY year DESC";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_3($year)
    {
        $sql = "SELECT *,project.name AS pj_name,team.name AS t_name FROM {$this->db_name}.project
        JOIN team ON project.team_id = team.team_id
		WHERE is_delete = 0 AND project.team_id !=0 AND YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_3_year()
    {
        $sql = "SELECT YEAR(date) AS year FROM {$this->db_name}.project
        WHERE is_delete = 0 AND team_id != 0
        GROUP BY YEAR(date)
        ORDER BY year DESC";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_4($year)
    {
        $sql = "SELECT project_id, name as pj_name, image_logo, image_cover, user.student_id, user.first_name, user.last_name FROM {$this->db_name}.project
		join user on project.user_id = user.user_id
		Where project.is_delete = 0 AND cluster_id = 0 AND team_id = 0 AND YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_project_4_year()
    {
        $sql = "SELECT YEAR(date) AS year FROM {$this->db_name}.project
        WHERE is_delete = 0 AND cluster_id = 0 AND team_id = 0
        GROUP BY YEAR(date)
        ORDER BY year DESC";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_data_pagination($year, $page, $limit)
    {
        $offset = ($page - 1) * $limit;
        $sql = "SELECT project_id, name as pj_name, image_logo, image_cover, user.student_id, user.first_name, user.last_name FROM {$this->db_name}.project
		join user on project.user_id = user.user_id
		Where project.is_delete = 0 AND cluster_id = 0 AND team_id = 0 AND YEAR(date) = $year
        LIMIT $limit OFFSET $offset";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_num_page($year)
    {
        $sql = "SELECT count(project_id) AS num_project FROM {$this->db_name}.project
		Where is_delete = 0 AND cluster_id = 0 AND team_id = 0 AND YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }
}
