<?php
defined('BASEPATH') OR exit('No direct script access allowed');
/**
 * Description of smis_model
 *
 * @author SHAWON
 */
class Super_Admin_Student_Model extends CI_Model {

    //put your code here

    public function select_student_all_types() {
        $sql = "SELECT * FROM smis_student_types ur WHERE ur.status = '1'";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /*
     * Select Student Information With Person Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student
     * @paremeter: $type_id = null, $start, $limit, $status, $short = null
     * @Return: $result type Array of Multiple Row
     */

    public function select_student($type_id = null, $start, $limit, $status, $short = null) {
        $sql = "SELECT * "
                . "FROM tbl_student as s, tbl_person as p WHERE p.person_id = s.person_id ";
        if ($type_id) {
            $sql .= "AND s.student_type = $type_id ";
        }
        if ($status != '') {
            $sql .= "AND p.person_status = $status ";
        }
        if ($short) {
            $sql .= " ORDER BY p.person_first_name $short ";
        }
        if ($limit != '' && $start >= 0) {
            $sql .= " LIMIT $start, $limit ";
        }
        
//        $sql .= " GROUP BY p.person_id ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /*
     * Select Student Information With Person Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_by_student_id
     * @paremeter: $student_id
     * @Return: $result type Array of Single row
     */

    public function select_student_by_student_id($person_id) {
        $sql = "SELECT * FROM tbl_student as s, tbl_person as p WHERE p.person_id = $person_id AND s.person_id = p.person_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /*
     * Select Student Course Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_course_by_student_id
     * @paremeter: $student_id
     * @Return: $result type Array of Multiple row
     */

    public function select_student_course_by_student_id($person_id, $student_type) {
//        $sql = "SELECT "
//                . "     s.student_id, "
//                . "     cb.course_batch_name, "
//                . "     c.course_start_date, "
//                . "     c.course_finished_date, "
//                . "     c.course_price, "
//                . "     c.course_id, "
//                . "     c.course_name, "
//                . "     cc.course_category_name, "
//                . "     cb.course_batch_id, "
//                . "     cb.course_batch_status, "
//                . "     pay.payment_id "
//                . " FROM "
//                . "     tbl_person as p,"
//                . "     tbl_student as s, "
//                . "     tbl_course_batch as cb, "
//                . "     tbl_course as c, "
//                . "     tbl_course_category as cc, "
//                . "     tbl_course_batch_student as cbs, "
//                . "     tbl_payment as pay "
//                . " WHERE "
//                . "         p.person_id = $person_id "
//                . "     AND p.person_id = s.person_id "
//                . "     AND s.student_id = cbs.student_id "
//                . "     AND cbs.batch_id = cb.course_batch_id "
//                . "     AND c.course_category_id = cc.course_category_id "
//                . "     AND cb.course_id = c.course_id "
////                . "     AND p.person_id = pay.person_id "
//                . "     AND s.payment_id = pay.payment_id "
//        ;
//
//        if ($student_type != 0) {
//            $sql .= " AND s.student_type = $student_type ";
//        }
//        $sql .= " GROUP BY c.course_id";
//        $result = $this->db->query($sql)->result();
////        echo '<pre>';
////        print_r($result);
////        exit();
//        return $result;


        $sql = "SELECT DISTINCT pay.payment_id, pay.*, sum(pdi.paid_amount) as total, pdi.*, c.*, cpn.*, cbs.*, cc.*"
                . " FROM "
                . "     tbl_payment as pay, "
                . "     tbl_payment_details_information as pdi,"
                . "     tbl_student as s, "
                . "     tbl_course as c,"
                . "     tbl_course_batch as cb, "
                . "     tbl_coupon as cpn, "
                . "     tbl_course_category as cc, "
                . "     tbl_course_batch_student as cbs "
                . " WHERE "
                . "         pay.person_id = $person_id "
                . "     AND pay.payment_id = pdi.payment_id "
                . "     AND pay.course_batch_id = cb.course_batch_id "
                . "     AND s.student_id = cbs.student_id "
                . "     AND c.course_id= cb.course_id "
                . "     AND c.course_category_id = cc.course_category_id "
                . "     AND cbs.batch_id = cb.course_batch_id "
                . "     AND cb.course_id = c.course_id "
                . "     AND cpn.coupon_id = pay.coupon_id "
                . "     GROUP BY pay.payment_id "
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_student_course_invoice_information_v2($person_id, $course_id) {
        $sql = "SELECT pay.*, sum(pdi.paid_amount) as total, pdi.*, c.*, cpn.* "
                . " FROM "
                . "     tbl_payment as pay, "
                . "     tbl_payment_details_information as pdi,"
                . "     tbl_course as c,"
                . "     tbl_course_batch as cb, "
                . "     tbl_coupon as cpn "
                . " WHERE "
                . "         pay.person_id = $person_id "
                . "     AND c.course_id= $course_id "
                . "     AND pay.payment_id = pdi.payment_id "
                . "     AND pay.course_batch_id = cb.course_batch_id "
                . "     AND c.course_id= cb.course_id "
                . "     GROUP BY c.course_id"
        ;
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    /*
     * Select Student Course Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_course_by_student_id
     * @paremeter: $student_id
     * @Return: $result type Array of Multiple row
     */

    public function select_all_available_coupon() {
        $sql = "SELECT * "
                . " FROM "
                . "      tbl_coupon as c"
                . " WHERE "
                . "      c.coupon_status = 1"
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    /*
     * Select Student Course Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_course_by_student_id
     * @paremeter: $student_id
     * @Return: $result type Array of Multiple row
     */

//    public function select_student_total_pay_for_course_by_course_id($course_batch_id, $person_id) {
//        $sql = "SELECT sum(pdi.paid_amount) as total, p.payment_id, p.payment_id, pdi.due_date, p.invoice_number, p.quantity"
//                . " FROM "
//                . "     tbl_payment as p, "
//                . "     tbl_payment_details_information as pdi"
//                . " WHERE "
//                . "         p.course_batch_id = $course_batch_id "
//                . "     AND p.person_id = $person_id "
//                . "     AND p.payment_id = pdi.payment_id";
//        $result = $this->db->query($sql)->row();
//        return $result;
//    }

    /*
     * Select Student Course Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_course_by_student_id
     * @paremeter: $student_id
     * @Return: $result type Array of Multiple row
     */

    public function select_student_total_pay_for_course_by_course_id($person_id, $payment_id) {
        $sql = "SELECT sum(pdi.paid_amount) as total, pdi.*, p.*, cpn.* "
                . " FROM "
                . "     tbl_payment as p, "
                . "     tbl_payment_details_information as pdi,"
                . "     tbl_coupon as cpn "
                . " WHERE "
                . "         p.payment_id = $payment_id "
                . "     AND p.person_id = $person_id "
                . "     AND p.payment_id = pdi.payment_id "
                . "     AND p.coupon_id = cpn.coupon_id "
        ;
//        echo '<pre>';
//        print_r($sql);
//        exit();
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /*
     * Select Student All Payment Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student_all_payment_information
     * @paremeter: $student_id
     * @Return: $result type Array of Multiple row
     */

    public function select_student_all_payment_information($student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_payment as p, "
                . "     tbl_payment_details_information as pdi, "
                . "     tbl_student as s"
                . " WHERE "
                . "         s.student_id = $student_id "
                . "     AND s.person_id = p.person_id "
                . "     AND p.payment_id = pdi.payment_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /*
     * Select Student Information With Person Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student
     * @paremeter: $type_id = null, $start, $limit, $status, $short = null
     * @Return: $result type Array of Multiple Row
     */

    public function select_student_all_invoice_information($person_id) {
//        $sql = "SELECT pay.*, sum(pdi.paid_amount) as total, pdi.*, c.* "
//                . " FROM "
//                . "     tbl_payment as pay, "
//                . "     tbl_payment_details_information as pdi,"
//                . "     tbl_course as c,"
//                . "     tbl_course_batch as cb"
//                . " WHERE "
//                . "         pay.person_id = $person_id"
//                . "     AND pay.payment_id = pdi.payment_id"
//                . "     AND pay.course_batch_id = cb.course_batch_id"
//                . "     AND c.course_id= cb.course_id"
//                . "     GROUP BY pdi.payment_id"
//        ;
//        $result = $this->db->query($sql)->result();
//        return $result;

        $sql = "SELECT DISTINCT pay.payment_id, pay.*, sum(pdi.paid_amount) as total, pdi.*, c.*, cpn.*"
                . " FROM "
                . "     tbl_payment as pay, "
                . "     tbl_payment_details_information as pdi,"
                . "     tbl_course as c,"
                . "     tbl_course_batch as cb, "
                . "     tbl_coupon as cpn "
                . " WHERE "
                . "         pay.person_id = $person_id "
                . "     AND pay.payment_id = pdi.payment_id "
                . "     AND pay.course_batch_id = cb.course_batch_id "
                . "     AND c.course_id= cb.course_id "
                . "     AND cpn.coupon_id = pay.coupon_id "
                . "     GROUP BY pay.payment_id "
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    /*
     * Select Save Student Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: save_student_information
     * @paremeter: no paremeter
     * @Return: $person_id, type single value
     */

    public function save_student_information() {

        $pdata = array();
        $pdata['person_email'] = $this->input->post('person_email', TRUE);
        $pdata['person_password'] = md5($this->input->post('person_user_password', TRUE));
        $pdata['person_first_name'] = $this->input->post('person_first_name', TRUE);
        $pdata['person_last_name'] = $this->input->post('person_last_name', TRUE);
        $pdata['person_gender'] = $this->input->post('person_gender', TRUE);
        $country_code = $this->input->post('country_code', TRUE);
        $pdata['person_contact_number'] = $country_code . ' ' . $this->input->post('person_contact_number', TRUE);
        $pdata['person_country'] = $this->input->post('person_country', TRUE);
        $pdata['person_city'] = $this->input->post('person_city', TRUE);
        $pdata['person_zip'] = $this->input->post('person_zip', TRUE);
        $pdata['person_address'] = $this->input->post('person_address', TRUE);
        $pdata['person_role'] = $this->input->post('person_role');
        $pdata['person_status'] = $this->input->post('person_status', TRUE);
        /*
         * END Store User Personal Information
         * Start Image Upload
         */
        $config['upload_path'] = 'site/images/user/original/';
        $config['allowed_types'] = 'gif|jpg|png';
        $config['max_size'] = '300';
        $config['max_width'] = '2000';
        $config['max_height'] = '2000';
        $error = '';
        $fdata = array();
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('image')) {
            $error = $this->upload->display_errors();
            echo $error;
            exit();
        } else {
            $fdata = $this->upload->data();
            $pdata['person_image'] = $config['upload_path'] . $fdata['file_name'];
        }
        //// Start Create Thumbnail
        $config['image_library'] = 'gd2';
        $config['new_image'] = 'site/images/student/thumbnail/';
        $config['source_image'] = $pdata['person_image'];
        $config['create_thumb'] = TRUE;
        $config['maintain_ratio'] = TRUE;
        $config['width'] = '100';
        $config['height'] = '100';
        $this->load->library('image_lib', $config);

        $this->image_lib->resize();

        if (!$this->image_lib->resize()) {
            $error = $this->image_lib->display_errors();
            echo $error;
            exit();
        } else {
            $pdata['person_image_thumb'] = $config['new_image'] . $fdata['raw_name'] . '_thumb' . $fdata['file_ext'];
        }
        //// END Create Thumbnail
        /*
         * End Image Ipload Store in tbl_person Table
         */
        $this->db->insert('tbl_person', $pdata);
        $person_id = $this->db->insert_id();

        return $person_id;
    }

    /*
     * Save Student Payment Information
     * @Author: Mahabub-A-Mawla Shawon
     * @Function Name: select_student
     * @paremeter: $type_id = null, $start, $limit, $status, $short = null
     * @Return: $result type Array of Multiple Row
     */

    public function save_student_payment_information() {

        /*
         * Select Course Information from tbl_course
         */
        $course_id = $this->session->userdata('course_id');
        $sql = "SELECT * FROM tbl_course as c WHERE c.course_id = $course_id";
        $course_information = $this->db->query($sql)->row();
//        $resource_person_id = $course_information->course_author;

        /*
         * START Payment Information
         */
        $payment_data = array();
        $payment_data['person_id'] = $this->session->userdata('new_student_id');
        $payment_data['course_batch_id'] = $this->session->userdata('course_batch_id');
        $payment_data['payment_name'] = $this->input->post('payment_name', TRUE);
        $payment_data['invoice_number'] = $this->input->post('invoice_number', TRUE);
        $payment_data['payment_description'] = $this->input->post('payment_description', TRUE);
        $payment_data['quantity'] = '1';
        $payment_data['unit_price'] = $course_information->course_price;
        $payment_data['total_price'] = $course_information->course_price;

        $payment_data['coupon_id'] = $this->input->post('coupon_id', TRUE);

        $this->db->insert('tbl_payment', $payment_data);
        $payment_id = $this->db->insert_id();

        /*
         * END Payment Information
         */

        /*
         * START Payment Detail Information
         */
        $payment_detail_data['payment_id'] = $payment_id;
        $payment_detail_data['billing_address'] = '';

        //Define Payment Status

        $payment_amount = $this->input->post('payment_amount', TRUE);
        if ($payment_amount < $course_information->course_price) {
            $payment_detail_data['payment_status'] = 2; //2 for Partial Paid
        } else if ($payment_amount == 0) {
            $payment_detail_data['payment_status'] = 3; //3 for Unpaid
        } else {
            $payment_detail_data['payment_status'] = 1; //1 for paid
        }

        $payment_detail_data['payment_method'] = $this->input->post('payment_method', TRUE);
        $payment_detail_data['paid_on'] = $this->input->post('paid_on', TRUE);
        $payment_detail_data['due_date'] = $this->input->post('due_date', TRUE);
        $payment_detail_data['paid_amount'] = $payment_amount;

        $payment_method = $this->input->post('payment_method', TRUE);
        if ($payment_method == 1) {//1 for Paypal
            $payment_detail_data['paypal_email_address'] = $this->input->post('paypal_email_address');
        } else if ($payment_method == 2) {//2 for Credit Card
            $payment_detail_data['credit_card_type'] = $this->input->post('credit_card_type');
            $payment_detail_data['credit_card_number'] = $this->input->post('credit_card_number');
            $payment_detail_data['credit_card_expiry_date'] = $this->input->post('credit_card_expiry_date');
            $payment_detail_data['credit_card_verification_number'] = $this->input->post('credit_card_verification_number');
        } else if ($payment_method == 3) {// 3 for Account
            $payment_detail_data['check_payment_receipt_no'] = $this->input->post('check_payment_receipt_no');
        } else if ($payment_method == 4) {// 4 for bKash
            $payment_detail_data['bkash_payment_no'] = $this->input->post('bkash_ payment_no');
        } else if ($payment_method == 5) {// 5 for Hand Cash
            $payment_detail_data['cash_payment_receipt_no'] = $this->input->post('cash_payment_receipt_no');
        }

        $this->db->insert('tbl_payment_details_information', $payment_detail_data);
        $payment_details_information_id = $this->db->insert_id();
        /*
         * END Payment Detail Information
         */

        /*
         * START Student Information
         */
        $student_data = array();
        $student_data['person_id'] = $this->session->userdata('new_student_id');
        $student_data['course_id'] = $this->session->userdata('course_id');
        $student_data['payment_id'] = $payment_id;
        $student_data['student_type'] = $this->session->userdata('student_type');
//        $student_data['payment_details_information_id'] = $payment_details_information_id;
        $this->db->insert('tbl_student', $student_data);
        $student_id = $this->db->insert_id();
        /*
         * END Student Information
         */

        /*
         * Save Student-Batch id 
         */
        $course_batch_id = $this->session->userdata('course_batch_id');
        $cb_data['student_id'] = $student_id;
        $cb_data['batch_id'] = $course_batch_id;
        $this->db->insert('tbl_course_batch_student', $cb_data);
        /*
         * END Save Student-Batch id 
         */
        return $payment_id;
    }

    public function save_student_due_payment_information($payment_id) {

        /*
         * Select Course Information from tbl_course
         */
        $course_id = $this->input->post('course_id', TRUE);
        $sql = "SELECT * FROM tbl_course as c WHERE c.course_id = $course_id";
        $course_information = $this->db->query($sql)->row();
        $resource_person_id = $course_information->course_author;

        /*
         * START Payment Detail Information
         */
        $payment_detail_data['payment_id'] = $payment_id;
        $payment_detail_data['billing_address'] = '';

        //Define Payment Status
        $payment_amount = $this->input->post('payment_amount', TRUE);
        if ($payment_amount < $course_information->course_price) {
            $payment_detail_data['payment_status'] = 2; //2 for Partial Paid
        } else if ($payment_amount == 0) {
            $payment_detail_data['payment_status'] = 3; //3 for Full Due
        } else {
            $payment_detail_data['payment_status'] = 1; //1 for paid
        }

        $payment_detail_data['payment_method'] = $this->input->post('payment_method', TRUE);
        $payment_detail_data['paid_on'] = $this->input->post('paid_on', TRUE);
        $payment_detail_data['due_date'] = $this->input->post('due_date', TRUE);
        $payment_detail_data['paid_amount'] = $payment_amount;

        $payment_method = $this->input->post('payment_method', TRUE);
        if ($payment_method == 1) {//1 for Paypal
            $payment_detail_data['paypal_email_address'] = $this->input->post('paypal_email_address');
        } else if ($payment_method == 2) {//2 for Credit Card
            $payment_detail_data['credit_card_type'] = $this->input->post('credit_card_type');
            $payment_detail_data['credit_card_number'] = $this->input->post('credit_card_number');
            $payment_detail_data['credit_card_expiry_date'] = $this->input->post('credit_card_expiry_date');
            $payment_detail_data['credit_card_verification_number'] = $this->input->post('credit_card_verification_number');
        } else if ($payment_method == 3) {// 3 for Account
            $payment_detail_data['check_payment_receipt_no'] = $this->input->post('check_payment_receipt_no');
        } else if ($payment_method == 4) {// 4 for bKash
            $payment_detail_data['bkash_payment_no'] = $this->input->post('bkash_ payment_no');
        } else if ($payment_method == 5) {// 5 for Hand Cash
            $payment_detail_data['cash_payment_receipt_no'] = $this->input->post('cash_payment_receipt_no');
        }

        $this->db->insert('tbl_payment_details_information', $payment_detail_data);
        return $this->db->insert_id();
        /*
         * END Payment Detail Information
         */
    }

    public function select_student_invoice_information_by_payment_id($payment_details_information_id) {
        $sql = "SELECT "
                . "     p.person_first_name "
                . "     p.person_last_name "
                . "     p.person_email "
                . "     p.person_contact_number "
                . "     p.person_address "
                . "     cb.course_batch_name, "
                . "     c.course_start_date, "
                . "     c.course_finished_date, "
                . "     c.course_price, "
                . "     c.course_id, "
                . "     c.course_name, "
                . "     cc.course_category_name, "
                . "     cb.course_batch_id,"
                . "     pdi.paid_amount"
                . "     pdi.unit_price"
                . "     pdi.unit_price"
                . " FROM "
                . "     tbl_payment as pay"
                . "     tbl_payment_details_information as pdi"
                . "     tbl_person as p,"
                . "     tbl_student as s, "
                . "     tbl_course_batch as cb, "
                . "     tbl_course as c, "
                . "     tbl_course_category as cc, "
                . "     tbl_course_batch_student as cbs "
                . " WHERE "
                . "         pdi.payment_details_information_id = $payment_details_information_id"
                . "     AND pay.payment_details_information_id = pdi.payment_details_information_id"
                . "     AND p.person_id = pdi.person_id "
                . "     AND pdi.course_batch_id = cb.course_batch_id "
                . "     AND cb.course_id = c.course_id "
                . "     AND c.course_category_id = cc.course_category_id "
        ;
    }

    public function update_person_information($person_id) {

        $pdata = array();
        $pdata['person_email'] = $this->input->post('person_email', TRUE);
        $pdata['person_password'] = md5($this->input->post('person_password', TRUE));
        $pdata['person_first_name'] = $this->input->post('person_first_name', TRUE);
        $pdata['person_last_name'] = $this->input->post('person_last_name', TRUE);
        $pdata['person_gender'] = $this->input->post('person_gender', TRUE);
        $country_code = $this->input->post('country_code', TRUE);
        $pdata['person_contact_number'] = $country_code . ' ' . $this->input->post('person_contact_number', TRUE);
        $pdata['person_country'] = $this->input->post('person_country', TRUE);
        $pdata['person_city'] = $this->input->post('person_city', TRUE);
        $pdata['person_zip'] = $this->input->post('person_zip', TRUE);
        $pdata['person_address'] = $this->input->post('person_address', TRUE);
        $pdata['person_role'] = $this->input->post('person_role');
        $pdata['person_status'] = $this->input->post('person_status', TRUE);
        /*
         * END Store User Personal Information
         *
         * Image Upload
         */
        if ($_FILES['person_new_image']['name']) {
            /*
             * Start Image Upload
             */
//            $this->load->library('media');
//            $pdata['user_image'] = $this->media->upload_image($_FILES);
            $config['upload_path'] = 'site/images/student/original/';
            $config['allowed_types'] = 'gif|jpg|png';
            $config['max_size'] = '3000';
            $config['max_width'] = '2000';
            $config['max_height'] = '2000';
            $error = '';
            $fdata = array();
            $this->load->library('upload', $config);

            if (!$this->upload->do_upload('person_new_image')) {
                $error = $this->upload->display_errors();
                echo $error;
                exit();
            } else {
                $fdata = $this->upload->data();
                $pdata['person_image'] = $config['upload_path'] . $fdata['file_name'];
            }
            //// Start Create Thumbnail
            $config['image_library'] = 'gd2';
            $config['new_image'] = 'site/images/student/thumbnail/';
            $config['source_image'] = $pdata['person_image'];
            $config['create_thumb'] = TRUE;
            $config['maintain_ratio'] = TRUE;
            $config['width'] = '100';
            $config['height'] = '100';
            $this->load->library('image_lib', $config);

            $this->image_lib->resize();

            if (!$this->image_lib->resize()) {
                $error = $this->image_lib->display_errors();
                echo $error;
                exit();
            } else {
                $pdata['person_image_thumb'] = $config['new_image'] . $fdata['raw_name'] . '_thumb' . $fdata['file_ext'];
            }
            //// END Create Thumbnail
            /*
             * End Image Ipload
             */
        } else {
            $pdata['person_image'] = $this->input->post('person_image', TRUE);
            $pdata['person_image_thumb'] = $this->input->post('person_image_thumb', TRUE);
        }
        //// END Create Thumbnail
        /*
         * End Image Ipload
         */
        $this->db->where('person_id', $person_id);
        $this->db->update('tbl_person', $pdata);
    }

    public function select_all_active_current_student() {
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE s.student_id=si.student_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_all_active_current_student_by_limit($start) {
        $end = $start + 10;
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE s.student_id=si.student_id AND s.type_id = st.type_id LIMIT $start, $end";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_all_students($start) {
        $end = $start + 10;
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE s.student_id=si.student_id AND s.type_id = st.type_id LIMIT $start, $end";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_student_information_by_id($person_id) {
        $sql = "SELECT p.*, s.student_id, p.person_email"
                . " FROM tbl_student as s, tbl_person as p "
                . "WHERE s.person_id = $person_id AND s.person_id = p.person_id";
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($sql);
//        exit();
        return $result;
    }
    public function select_person_information_by_id($person_id) {
        $sql = "SELECT p.* "
                . " FROM "
                . "     tbl_person as p "
                . "WHERE p.person_id = $person_id";
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_student_information_by_person_id($person_id) {
        $sql = "SELECT pi.*, s.email, s.type_id, s.status, s.created_by, s.created_date_time, s.updated_by, s.updated_date_time "
                . "FROM smis_student s, tbl_person_information pi "
                . "WHERE s.person_id = $person_id AND s.person_id = pi.person_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_students_by_student_role_id($type_id = null) {
        if ($type_id == '0') {
            $sql = "SELECT * "
                    . "FROM smis_student s, smis_student_information si, smis_student_types st "
                    . "WHERE s.student_id = si.student_id AND s.type_id = st.type_id";
        } else {
            $sql = "SELECT * "
                    . "FROM smis_student s, smis_student_information si, smis_student_types st "
                    . "WHERE s.type_id = $type_id AND s.student_id=si.student_id AND s.type_id = st.type_id";
        }

        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_students_by_student_role_id_limit($type_id, $start) {
        $end = $start + 10;
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE s.type_id = $type_id AND s.student_id=si.student_id AND s.type_id = st.type_id LIMIT $start, $end";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_students_by_role_and_status_limit($type_id = null, $status = null, $Start) {
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE s.type_id = $type_id AND u.status = $status AND s.student_id=si.student_id AND s.type_id = st.type_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_students_by_student_status($status) {
        $sql = "SELECT * "
                . "FROM smis_student s, smis_student_information si, smis_student_types st "
                . "WHERE u.status = $status AND s.student_id=si.student_id AND s.type_id = st.type_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_shorted_student_list_by_role_id($type_id = NULL) {
        if ($student_role_id != 0) {
            $sql = "SELECT * "
                    . "FROM smis_student s, smis_student_information si, smis_student_types st "
                    . "WHERE s.type_id = $type_id AND s.student_id = si.student_id AND s.type_id = st.type_id ORDER BY first_name";
        } else {
            $sql = "SELECT * "
                    . "FROM smis_student s, smis_student_information si, smis_student_types st "
                    . "WHERE s.student_id=si.student_id AND s.type_id = st.type_id ORDER BY first_name";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function make_student_archived($person_id) {
        $this->db->set('person_status', '2');
        $this->db->where('person_id', $person_id);
        $this->db->update('tbl_person');
    }

    public function make_student_active($person_id) {
        $this->db->set('person_status', '1');
        $this->db->where('person_id', $person_id);
        $this->db->update('tbl_person');
    }

    public function make_student_inactive($person_id) {
        $this->db->set('person_status', '0');
        $this->db->where('person_id', $person_id);
        $this->db->update('tbl_person');
    }

    public function assign_course_to_student($student_list, $course_id) {
        $data = array();
        $data['course_id'] = $course_id;
        foreach ($student_list as $v_student) {
            $data['student_id'] = $v_student;
            $this->db->insert('cmis_course_student', $data);
        }
    }

    public function set_student_role($person_list, $role_id) {

        for ($i = 0; $i < count($person_list); $i++) {
            $this->db->set('person_role', $role_id);
            $this->db->where('person_id', $person_list[$i]);
            $this->db->update('tbl_person'); /*
             * Insert Person Id in Particular Role Table
             */
            $data = array();
            if ($role_id == '1') {
                $data['person_id'] = $person_list[$i];
                $this->db->insert('tbl_admin_user', $data);
            } else if ($role_id == '2') {
                $data['person_id'] = $person_list[$i];
                $this->db->insert('tbl_account_officer', $data);
            } else if ($role_id == '3') {
                $data['person_id'] = $person_list[$i];
                $this->db->insert('tbl_resource_person', $data);
            }
        }
    }

    public function change_persons_status($user_id_list, $status) {
        for ($i = 0; $i < count($user_id_list); $i++) {
            $this->db->set('person_status', $status);
            $this->db->where('person_id', $user_id_list[$i]);
            $this->db->update('tbl_person');
        }
    }

}

?>
