<?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 status = 1 and team_id != 0 and YEAR(date) = $year) AS team, 
            (SELECT count(project_id) AS project FROM project
		        WHERE status = 1 and team_id = 0 AND cluster_id = 0 and YEAR(date) = $year) AS project,
            (SELECT count(project_id) AS all_project FROM project
                WHERE status = 1) AS all_project,
            (SELECT count(project_id) as by_year FROM project
		        Where status = 1 and YEAR(date) = $year) as by_year
		WHERE status = 1 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_th as pj_name,cluster.name AS ct_name FROM {$this->db_name}.project
        JOIN cluster ON project.cluster_id = cluster.cluster_id
		WHERE status = 1 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 status = 1
        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 status = 1 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_th as pj_name,team.name AS t_name FROM {$this->db_name}.project
        JOIN team ON project.team_id = team.team_id
		WHERE status = 1 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 status = 1 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_th 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.status = 1 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 status = 1 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_th 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.status = 1 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_data_search_pagination($year, $grade, $cluster, $team, $company, $pname, $tag, $page, $limit)
    {
        $tag = ($tag == '') ? $tag = 'AND (project_tag.tag_id = project_tag.tag_id OR project_tag.tag_id IS NULL)' : $tag = 'AND project_tag.tag_id IN (' . $tag . ')';
        $offset = ($page - 1) * $limit;
        $sql = "SELECT *, project.project_id as project_id
        FROM project
        LEFT OUTER JOIN project_tag ON project_tag.project_id = project.project_id
        WHERE 
            (project_tag.project_id IS NULL OR project_tag.project_id = project_tag.project_id)
            $pname
            AND YEAR(date) = $year
            AND company_id = $company
            AND cluster_id = $cluster
            AND team_id = $team
            $grade
            AND status = 1
            $tag 
            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 status = 1 AND cluster_id = 0 AND team_id = 0 AND YEAR(date) = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_num_search_page($year, $grade, $cluster, $team, $company, $pname, $tag)
    {
        $tag = ($tag == '') ? $tag = 'AND (project_tag.tag_id = project_tag.tag_id OR project_tag.tag_id IS NULL)' : $tag = 'AND project_tag.tag_id IN (' . $tag . ')';
        $sql = "SELECT count(project.project_id) AS num_project
        FROM project
        LEFT JOIN project_tag ON project_tag.project_id = project.project_id
        WHERE 
            (project_tag.project_id IS NULL OR project_tag.project_id = project_tag.project_id)
            $pname
            AND YEAR(date) = $year
            AND company_id = $company
            AND cluster_id = $cluster
            AND team_id = $team
            $grade
            AND status = 1
            $tag
        ";
        $query = $this->db->query($sql);
        // print_r($sql);
        return $query;
    }

    public function get_search($year, $grade, $cluster, $team, $company, $pname, $tag)
    {
        $sql = "SELECT *
        FROM project
        LEFT JOIN project_tag ON project_tag.project_id = project.project_id
        WHERE 
            (project_tag.project_id IS NULL OR project_tag.project_id = project_tag.project_id)
            AND name = $pname
            AND YEAR(date) = $year
            AND company_id = $company
            AND cluster_id = $cluster
            AND team_id = $team
            $grade
            AND status = 1
            AND (project_tag.tag_id = $tag OR $tag IS NULL) -- Optional: include a tag filter
        ";
        $query = $this->db->query($sql);
        return $query;
    }
}
