<?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 
        (SELECT COUNT(project_id) FROM {$this->db_name}.project WHERE status = 1) AS all_project,
        (SELECT COUNT(project_id) FROM {$this->db_name}.project WHERE status = 1 AND date = $year) AS by_year,
        (SELECT COUNT(project_id) FROM {$this->db_name}.project WHERE status = 1 AND cluster_id != 0 AND date = $year) AS cluster,
        (SELECT COUNT(project_id) FROM {$this->db_name}.project WHERE status = 1 AND team_id != 0 AND date = $year) AS team,
        (SELECT COUNT(project_id) FROM {$this->db_name}.project WHERE status = 1 AND team_id = 0 AND cluster_id = 0 AND date = $year) AS project;";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_trend()
    {
        $sql = "SELECT *, project.name_th as pj_name, cluster.name AS ct_name, company.name AS cp_name, team.name AS t_name, instructor.name AS is_name, user.prefix, user.first_name, user.last_name FROM {$this->db_name}.project 
        JOIN user on project.user_id = user.user_id
        JOIN team ON project.team_id = team.team_id
        JOIN cluster ON project.cluster_id = cluster.cluster_id
        JOIN company ON project.company_id = company.company_id
        JOIN instructor ON project.instructor_id = instructor.instructor_id
        WHERE project.status = 1
        ORDER BY project_id DESC LIMIT 4";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_data_search_pagination_search($s_value, $page, $limit)
    {
        $offset = ($page - 1) * $limit;

        $sql = "SELECT *,cluster.name AS ct_name, company.name AS cp_name, team.name AS t_name, instructor.name AS is_name, user.prefix, user.first_name, user.last_name, project.project_id as project_id
        FROM {$this->db_name}.project
        LEFT OUTER JOIN project_tag ON project_tag.project_id = project.project_id
        JOIN user on project.user_id = user.user_id
        JOIN team ON project.team_id = team.team_id
        JOIN cluster ON project.cluster_id = cluster.cluster_id
        JOIN company ON project.company_id = company.company_id
        JOIN instructor ON project.instructor_id = instructor.instructor_id
        WHERE project.status = 1 
        $s_value
        LIMIT $limit OFFSET $offset";
        $query = $this->db->query($sql);
        return $query;
    }


    public function get_data_chart($year)
    {
        $sql = "SELECT company.name AS cp_name, COUNT(project.company_id) AS num FROM project 
        JOIN company ON project.company_id = company.company_id
        WHERE project.status = 1 $year
        GROUP BY project.company_id";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_num_project_by_search($s_value)
    {

        $sql = "SELECT count(project.project_id) AS num_project
        FROM {$this->db_name}.project
        LEFT OUTER JOIN project_tag ON project_tag.project_id = project.project_id
        WHERE project.status = 1 
        $s_value
        ";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_tag()
    {
        $sql = "SELECT tag.tag_id AS id, tag.name AS tag, COUNT(project_tag.tag_id) AS num  FROM  {$this->db_name}.project_tag 
        JOIN tag ON project_tag.tag_id = tag.tag_id
        JOIN project ON project_tag.project_id = project.project_id
        WHERE project.status = 1
        GROUP BY tag.tag_id";
        $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, company.name AS cp_name FROM {$this->db_name}.project
        JOIN cluster ON project.cluster_id = cluster.cluster_id
        JOIN company ON project.company_id = company.company_id
		WHERE status = 1 AND project.cluster_id != 0 AND date = $year";
        $query = $this->db->query($sql);
        return $query;
    }

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

    public function get_project_2_year()
    {
        $sql = "SELECT date AS year FROM {$this->db_name}.project
        WHERE status = 1 AND cluster_id != 0
        GROUP BY 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, company.name AS cp_name FROM {$this->db_name}.project
        JOIN team ON project.team_id = team.team_id
        JOIN company ON project.company_id = company.company_id
		WHERE status = 1 AND project.team_id !=0 AND date = $year";
        $query = $this->db->query($sql);
        return $query;
    }

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

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

    public function get_tag_project($id)
    {
        $sql = "SELECT tag.name AS tag_name FROM {$this->db_name}.project_tag 
        JOIN tag ON tag.tag_id = project_tag.tag_id
        WHERE project_tag.project_id = $id";
        $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.prefix, user.first_name, user.last_name, company.name AS cp_name, instructor.name AS is_name FROM {$this->db_name}.project
		JOIN user on project.user_id = user.user_id
        JOIN company ON project.company_id = company.company_id
        JOIN instructor ON project.instructor_id = instructor.instructor_id
		WHERE project.status = 1 AND cluster_id = 0 AND team_id = 0 AND 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)
    {
        $having_count = ($tag == '') ? $having_count = '' : $having_count = 'HAVING COUNT(DISTINCT project_tag.tag_id) = ' . count($tag);
        ($tag == '') ?: $tag = implode(',', $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 . ')';
        $offset = ($page - 1) * $limit;

        $sql = "SELECT *,cluster.name AS ct_name, company.name AS cp_name, team.name AS t_name, instructor.name AS is_name, user.prefix, user.first_name, user.last_name, project.project_id as project_id
        FROM {$this->db_name}.project
        LEFT OUTER JOIN project_tag ON project_tag.project_id = project.project_id
        JOIN user on project.user_id = user.user_id
        JOIN team ON project.team_id = team.team_id
        JOIN cluster ON project.cluster_id = cluster.cluster_id
        JOIN company ON project.company_id = company.company_id
        JOIN instructor ON project.instructor_id = instructor.instructor_id
        WHERE 
            (project_tag.project_id IS NULL OR project_tag.project_id = project_tag.project_id)
            $pname
            AND date = $year
            AND project.company_id = $company
            AND project.cluster_id = $cluster
            AND project.team_id = $team
            $grade
            AND status = 1
            $tag 
            GROUP BY project.project_id
            $having_count
            LIMIT $limit OFFSET $offset
            ";
        $query = $this->db->query($sql);
        // echo '<pre>';
        // print_r($sql);
        // echo '</pre>';
        // echo '<br>==========================<br>';
        // echo '<pre>';
        // print_r($query);
        // echo '</pre>';
        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 date = $year";
        $query = $this->db->query($sql);
        return $query;
    }

    public function get_num_search_page($year, $grade, $cluster, $team, $company, $pname, $tag)
    {
        $having_count = ($tag == '') ? $having_count = '' : $having_count = 'HAVING COUNT(DISTINCT project_tag.tag_id) = ' . count($tag);
        ($tag == '') ?: $tag = implode(',', $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(*) as num_project FROM (SELECT project.project_id
        FROM {$this->db_name}.project
        LEFT OUTER JOIN project_tag ON project_tag.project_id = project.project_id
        JOIN user on project.user_id = user.user_id
        JOIN team ON project.team_id = team.team_id
        JOIN cluster ON project.cluster_id = cluster.cluster_id
        JOIN company ON project.company_id = company.company_id
        JOIN instructor ON project.instructor_id = instructor.instructor_id
        WHERE 
            (project_tag.project_id IS NULL OR project_tag.project_id = project_tag.project_id)
            $pname
            AND date = $year
            AND project.company_id = $company
            AND project.cluster_id = $cluster
            AND project.team_id = $team
            $grade
            AND status = 1
            $tag 
            GROUP BY project.project_id
            $having_count
        ) as subquery";
        $query = $this->db->query($sql);
        // echo '<pre>';
        // print_r($sql);
        // echo '</pre>';
        // echo '<br>==========================<br>';
        // echo '<pre>';
        // print_r($query);
        // echo '</pre>';

        return $query;
    }

    public function get_search($year, $grade, $cluster, $team, $company, $pname, $tag)
    {
        $sql = "SELECT *
        FROM {$this->db_name}.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 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;
    }

    public function get_comment_all($id)
    {
        $sql = "SELECT * FROM {$this->db_name}.comment
		Where project_id = $id order by comment_id DESC";
        $query = $this->db->query($sql);
        return $query;
    }
}
