M_general.php

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