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

/**
 * CMS Sekolahku | CMS (Content Management System) dan PPDB/PMB Online GRATIS
 * untuk sekolah SD/Sederajat, SMP/Sederajat, SMA/Sederajat, dan Perguruan Tinggi
 * @version    2.4.9
 * @author     Anton Sofyan | https://facebook.com/antonsofyan | 4ntonsofyan@gmail.com | 0857 5988 8922
 * @copyright  (c) 2014-2020
 * @link       https://sekolahku.web.id
 *
 * PERINGATAN :
 * 1. TIDAK DIPERKENANKAN MENGGUNAKAN CMS INI TANPA SEIZIN DARI PIHAK PENGEMBANG APLIKASI.
 * 2. TIDAK DIPERKENANKAN MEMPERJUALBELIKAN APLIKASI INI TANPA SEIZIN DARI PIHAK PENGEMBANG APLIKASI.
 * 3. TIDAK DIPERKENANKAN MENGHAPUS KODE SUMBER APLIKASI.
 */

class M_students extends CI_Model {

	/**
	 * Primary key
	 * @var String
	 */
	public static $pk = 'id';

	/**
	 * Table
	 * @var String
	 */
	public static $table = 'students';

	/**
	 * Class Constructor
	 *
	 * @return Void
	 */
	public function __construct() {
		parent::__construct();
	}

	/**
	 * Get Data
	 * @param String $keyword
	 * @param String $return_type
	 * @param Integer $limit
	 * @param Integer $offset
	 * @return Resource
	 */
	public function get_where($keyword = '', $return_type = 'count', $limit = 0, $offset = 0) {
		$this->db->select("
			x1.id
			, COALESCE(x1.identity_number, '') identity_number
			, x1.full_name
			, x2.option_name AS student_status
			, x1.gender
			, COALESCE(x1.birth_place, '') birth_place
			, x1.birth_date
			, x1.photo
			, x1.is_deleted
			");
		$this->db->join('options x2', 'x1.student_status_id = x2.id', 'LEFT');
		$this->db->where('x1.is_student', 'true');
		$this->db->where('x1.is_alumni', 'false');
		if ( ! empty($keyword) ) {
			$this->db->group_start();
			$this->db->like('x1.identity_number', $keyword);
			$this->db->or_like('x1.full_name', $keyword);
			$this->db->or_like('x2.option_name', $keyword);
			$this->db->or_like('x1.gender', $keyword);
			$this->db->or_like('x1.birth_place', $keyword);
			$this->db->or_like('x1.birth_date', $keyword);
			$this->db->group_end();
		}
		if ( $return_type == 'count' ) return $this->db->count_all_results(self::$table . ' x1');
		if ( $limit > 0 ) $this->db->limit($limit, $offset);
		return $this->db->get(self::$table . ' x1');
	}

	/**
	 * Chart by Student Status
	 * @param Integer $academic_year_id
	 * @return Array
	 */
	public function chart_by_student_status($academic_year_id) {
		// Sub Query
		$this->db->select('x1.student_id');
		$this->db->join('class_group_settings x2', 'x1.class_group_setting_id = x2.id', 'LEFT');
		$this->db->where('x2.academic_year_id', _toInteger($academic_year_id));
		$this->db->group_by('x1.student_id');
		$student_ids = $this->db->get_compiled_select('class_group_students x1');
		// Query
		$this->db->select('x2.option_name AS labels, COUNT(*) AS data');
		$this->db->join('options x2', 'ON x1.student_status_id = x2.id', 'LEFT');
		$this->db->where('x1.is_student', 'true');
		$this->db->where('x1.is_student', 'true');
		$this->db->where('x1.is_alumni', 'false');
		$this->db->where('x1.id IN (' . $student_ids . ')');
		$this->db->group_by('x2.option_name');
		$this->db->order_by('x2.option_name', 'ASC');
		return $this->db->get('students x1');
	}

	/**
	 * Chart by Class Group
	 * @param Integer $academic_year_id
	 * @return Array
	 */
	public function chart_by_class_groups($academic_year_id) {
		return $this->db->query("
			SELECT CONCAT(x3.class_group, IF((x4.major_short_name <> ''), CONCAT(' ', x4.major_short_name),''),IF((x3.sub_class_group <> ''),CONCAT(' - ',x3.sub_class_group),'')) AS labels
				, COUNT(*) AS data
			FROM class_group_students x1
			LEFT JOIN class_group_settings x2
				ON x1.class_group_setting_id = x2.id
			LEFT JOIN class_groups x3
				ON x2.class_group_id = x3.id
			LEFT JOIN majors x4
				ON x3.major_id = x4.id
			WHERE x2.academic_year_id = ?
			GROUP BY 1
		", [_toInteger($academic_year_id)]);
	}

	/**
	 * Chart by End Date
	 * @return Array
	 */
	public function chart_by_end_date() {
		return $this->db->query("
			SELECT LEFT(x1.end_date, 4) AS labels
				, COUNT(*) AS data
			FROM students x1
			WHERE x1.is_alumni='true'
			AND x1.is_deleted='false'
			GROUP BY 1
			ORDER BY 1 ASC
		");
	}

	/**
	 * Update student to Alumni
	 * @param Array $ids
	 * @param String $end_date
	 * @return Boolean
	 */
	public function set_as_alumni($ids, $end_date) {
		$student_status_id = get_option_id('student_status', 'lulus');
		$dataset = [];
		$dataset['is_alumni'] = 'true';
		$dataset['end_date'] = $end_date.'-05-01';
		if ($student_status_id > 0) {
			$dataset['student_status_id'] = $student_status_id;
		}
		return $this->db
			->where_in('id', $ids)
			->update(self::$table, $dataset);
	}

	/**
	 * Student Reports
	 * @return Resource
	 */
	public function student_reports() {
		$query = $this->student_query();
		$query .= "
		AND x1.is_student='true'
		AND x1.is_alumni='false'
		ORDER BY x1.identity_number, x1.full_name ASC";
		return $this->db->query($query);
	}

	/**
	 * Student Query
	 * @return String
	 */
	public function student_query() {
		return "
			SELECT x1.id
				, x2.major_name
				, x3.major_name AS first_choice
				, x4.major_name AS second_choice
				, x1.registration_number
				, x1.created_at
				, x1.admission_exam_number
				, x1.prev_exam_number
				, CASE WHEN x1.selection_result IS NULL THEN 'Belum Diseleksi'
					WHEN x1.selection_result = 'approved' THEN 'Diterima'
					WHEN x1.selection_result = 'unapproved' THEN 'Tidak Diterima'
					WHEN x1.selection_result > 0 THEN (SELECT major_name FROM majors WHERE id = x1.selection_result)
					ELSE '-'
					END AS selection_result
				, x5.phase_name
				, x6.option_name AS admission_type
				, x1.photo
				, IF(x1.is_transfer = 'true', 'Pindahan', 'Baru') AS is_transfer
				, x1.achievement
				, IF(x1.re_registration = 'true', 'Ya', 'Tidak') AS re_registration
				, x1.start_date
				, x1.identity_number
				, x1.nisn
				, x1.nik
				, x1.prev_diploma_number
				, IF(x1.paud = 'true', 'Ya', 'Tidak') AS paud
				, IF(x1.tk = 'true', 'Ya', 'Tidak') AS tk
				, x1.skhun
				, x1.prev_school_name
				, x1.prev_school_address
				, x1.hobby
				, x1.ambition
				, x1.full_name
				, IF(x1.gender = 'M', 'Laki-laki', 'Perempuan') AS gender
				, x1.birth_place
				, x1.birth_date
				, x7.option_name AS religion
				, x8.option_name AS special_need
				, x1.street_address
				, x1.rt
				, x1.rw
				, x1.sub_village
				, x1.village
				, x1.sub_district
				, x1.district
				, x1.postal_code
				, x9.option_name AS residence
				, x10.option_name AS transportation
				, x1.phone
				, x1.mobile_phone
				, x1.email
				, x1.sktm
				, x1.kks
				, x1.kps
				, x1.kip
				, x1.kis
				, x1.citizenship
				, x1.country
				, x1.father_name
				, x1.father_birth_year
				, x11.option_name AS father_education
				, x12.option_name AS father_employment
				, x13.option_name AS father_monthly_income
				, x14.option_name AS father_special_need
				, x1.mother_name
				, x1.mother_birth_year
				, x15.option_name AS mother_education
				, x16.option_name AS mother_employment
				, x17.option_name AS mother_monthly_income
				, x18.option_name AS mother_special_need
				, x1.guardian_name
				, x1.guardian_birth_year
				, x19.option_name AS guardian_education
				, x20.option_name AS guardian_employment
				, x21.option_name AS guardian_monthly_income
				, x1.mileage
				, x1.traveling_time
				, x1.height
				, x1.weight
				, x1.sibling_number
				, x22.option_name AS student_status
				, x1.end_date
				, x1.reason
			FROM students x1
			LEFT JOIN majors x2 ON x1.major_id = x2.id
			LEFT JOIN majors x3 ON x1.first_choice_id = x3.id
			LEFT JOIN majors x4 ON x1.second_choice_id = x4.id
			LEFT JOIN admission_phases x5 ON x1.admission_phase_id = x5.id
			LEFT JOIN options x6 ON x1.admission_type_id = x6.id
			LEFT JOIN options x7 ON x1.religion_id = x7.id
			LEFT JOIN options x8 ON x1.special_need_id = x8.id
			LEFT JOIN options x9 ON x1.residence_id = x9.id
			LEFT JOIN options x10 ON x1.transportation_id = x10.id
			LEFT JOIN options x11 ON x1.father_education_id = x11.id
			LEFT JOIN options x12 ON x1.father_employment_id = x12.id
			LEFT JOIN options x13 ON x1.father_monthly_income_id = x13.id
			LEFT JOIN options x14 ON x1.father_special_need_id = x14.id
			LEFT JOIN options x15 ON x1.mother_education_id = x15.id
			LEFT JOIN options x16 ON x1.mother_employment_id = x16.id
			LEFT JOIN options x17 ON x1.mother_monthly_income_id = x17.id
			LEFT JOIN options x18 ON x1.mother_special_need_id = x18.id
			LEFT JOIN options x19 ON x1.guardian_education_id = x19.id
			LEFT JOIN options x20 ON x1.guardian_employment_id = x20.id
			LEFT JOIN options x21 ON x1.guardian_monthly_income_id = x21.id
			LEFT JOIN options x22 ON x1.student_status_id = x22.id
			WHERE 1=1
		";
	}

	/**
	 * Get Inactive Accounts
	 * @return Resource
	 */
	public function get_inactive_accounts() {
		$this->db->select('x1.id, x1.identity_number, x1.full_name, x1.email');
		$this->db->join('users x2', 'x1.id = x2.user_profile_id AND x2.user_type = "student"', 'LEFT');
		$this->db->where('x2.user_profile_id', NULL);
		$this->db->where('x1.is_student', 'true');
		$this->db->where('x1.is_deleted', 'false');
		return $this->db->get('students x1');
	}

	/**
	 * Student Profile
	 * @param Integer $id
	 * @return Resource
	 */
	public function profile($id) {
		$query = $this->student_query();
		$query .= '
		AND x1.id = ?
		';
		return $this->db->query($query, [_toInteger($id)])->row();
	}

	/**
	 * Identity Number Exists ?
	 * @param Integer $identity_number
	 * @param Integer $id
	 * @return Boolean
	 */
	public function identity_number_exists($identity_number, $id = 0) {
		$this->db->where('identity_number', $identity_number);
		if ( _isNaturalNumber($id) ) $this->db->where('id <>', _toInteger($id));
		$this->db->where('is_deleted', 'false');
		$count = $this->db->count_all_results(self::$table);
		return $count > 0;
	}

	/**
	 * Check if nik exists
	 * @param String $nik
	 * @param Integer $id
	 * @return Boolean
	 */
	public function nik_exists( $nik, $id = 0 ) {
		$this->db->where('nik', $nik);
		if ( _isNaturalNumber($id) ) $this->db->where('id <>', _toInteger($id));
		$this->db->where('is_deleted', 'false');
		$count = $this->db->count_all_results(self::$table);
		return $count > 0;
	}

	/**
	 * Check if email exists
	 * @param String $email
	 * @param Integer $id
	 * @return Boolean
	 */
	public function email_exists( $email, $id = 0 ) {
		$this->db->where('email', $email);
		if ( _isNaturalNumber($id) ) $this->db->where('id <>', _toInteger($id));
		$this->db->where('is_deleted', 'false');
		$count = $this->db->count_all_results(self::$table);
		return $count > 0;
	}
}
