<?php

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

class Certificate_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    /*
     * @Function            : save_certificate_model 
     * @Description         : saves certificate data into database
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 28-01-17
     * @Parameter           : NULL      
     * @Status              : Available       
     */
    public function save_certificate_model(){
        $data = array(
            'person_id' => $this->session->person_id,
            'certificate_name' => $this->input->post('certificate_name', TRUE),
            'certificate_image' => $this->session->uploaded_certificate_logo,
            'assign_to_type' => $this->session->certificate_assign_to_type,
            'assign_to_type_id' => $this->session->certificate_assign_to_type_id,
            'certificate_template' => $this->input->post('certificate_template', TRUE),
            'certificate_status' => $this->input->post('certificate_status', TRUE)
        );
        $this->db->insert('tbl_certificate', $data);
        
    }
    /*
     * @Function            : get_course_info_by_url 
     * @Description         : get the course information from database based 
     *                      : on url
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 01-02-17
     * @Parameter           : $course_url      
     * @Status              : Available       
     */ 
    public function get_course_info_by_url($course_url){ 
        $url_data = $this->db->select('url_id')->from('tbl_url')
                         ->where('url', $course_url)
                         ->where('url_type', 5)->get()->row();
        $course_info = $this->db->select('*')->from('tbl_course')
                            ->where('url_id', $url_data->url_id)->get()->row();
        return $course_info;
    }
    
    
    /*
     * @Function            : get_certificate_info 
     * @Description         : get the certificate info from database using certificate id
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 01-02-17
     * @Parameter           : $certificate_id 
     * @Status              : Available       
     */
    public function get_certificate_info_by_id($certificate_id){        
        $result = $this->db->select('*')
                       ->from('tbl_certificate')                       
                       ->where('certificate_id', $certificate_id)
                       ->get()->row();
        return $result;
    }
    
    /*
     * @Function            : get_certificate_info 
     * @Description         : get the course info from database using course id
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 01-02-17
     * @Parameter           : $course_id 
     * @Status              : Available       
     */
    public function get_course_info_by_id($course_id){
        $result = $this->db->select('*')->from('tbl_course')
                      ->where('course_id', $course_id)
                      ->get()->row();
        return $result;
    }
    
    
    /*
     * @Function            : get_resource_person_info 
     * @Description         : get the resource person info from database using resource_person_id
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 02-02-17
     * @Parameter           : $resource_person_id 
     * @Status              : Available       
     */
    public function get_resource_person_info($resource_person_id){
        $result = $this->db->select('trp.resource_person_id, trp.person_id, trp.resource_person_type, '
                                    . 'tp.person_first_name, tp.person_last_name')
                           ->from('tbl_resource_person AS trp')
                           ->join('tbl_person AS tp', 'tp.person_id = trp.person_id')
                           ->where('trp.resource_person_id', $resource_person_id)
                           ->get()->row();
        return $result;       
    }
    
    
    /*
     * @Function            : get_organization_name 
     * @Description         : get the organization name using resourse person id
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 02-02-17
     * @Parameter           : $resource_person_id 
     * @Status              : Available       
     */
    public function get_organization_name($org_person_id){  
        $result = $this->db->select('person_first_name, person_last_name')
                           ->from('tbl_person')
                           ->where('person_id', $org_person_id)
                           ->get()->row();
        return $result->person_first_name.' '.$result->person_last_name;
        
        
    }  
    
    
    /*
     * @Function            : check_certificate 
     * @Description         : checks certificate existance for certain course
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 02-02-17
     * @Parameter           : $assign_to_type, $assign_to_type_id
     * @Status              : Available       
     */ 
    public function check_certificate($assign_to_type, $assign_to_type_id){
        $result = $this->db->select('*')->from('tbl_certificate')
                        ->where('assign_to_type', $assign_to_type)
                        ->where('assign_to_type_id', $assign_to_type_id)
                        ->get()->row();
        return $result;
    }
    
    
        /*
     * @Function            : update_certificate_model 
     * @Description         : update certificate info in database
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 02-02-17
     * @Parameter           : Null
     * @Status              : Available       
     */ 
    public function update_certificate_model(){
        $assign_to_type = $this->session->certificate_assign_to_type;
        $assign_to_type_id = $this->session->certificate_assign_to_type_id;        
        $certificate_id = $this->session->certificate_id;
        
        $data['certificate_name'] = $this->input->post('certificate_name');
        $data['certificate_image'] = $this->session->uploaded_certificate_logo;        
        $data['certificate_template'] = $this->input->post('certificate_template');
        $data['certificate_status'] = $this->input->post('certificate_status');
        // insert data
        $this->db->where('assign_to_type', $assign_to_type)
                 ->where('assign_to_type_id', $assign_to_type_id)
                 ->where('certificate_id', $certificate_id)
                 ->update('tbl_certificate', $data);
    }
    
/*---------------- Permanent Certification start Here -------------------*/
    
    
/*
 * @Function            : get_course_info_by_batch_id
 * @Description         : get the course info by batch id
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 26-02-17
 * @Parameter           : $batch_id        
 * @Status              : Available       
 */
    public function get_course_info_by_batch_id($batch_id){
        $result = $this->db->select('tc.course_id, tc.course_name')->from('tbl_course_batch AS tcb')
                                        ->join('tbl_course AS tc', 'tc.course_id = tcb.course_id')
                                        ->where('course_batch_id', $batch_id)
                                        ->get()->row();
        return $result;
    }
    
/*
 * @Function            : get_student_info
 * @Description         : get batch student info using student id string
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 26-02-17
 * @Parameter           : $student_id        
 * @Status              : Available       
 */
    public function get_student_info($batch_student_id){
        $student_array = explode(',', $batch_student_id);
        $result = $this->db->select('ts.student_roll_number, '
                                     . 'tp.person_first_name, '
                                     . 'tp.person_last_name, '
                                     . 'tp.person_email, '
                                     . 'tp.person_contact_number, '
                                     . 'tsc.student_certificate_id, '
                                     . 'ts.student_id')
                           ->from('tbl_student AS ts')
                           ->join('tbl_person AS tp', 'tp.person_id = ts.person_id')
                           ->join('tbl_student_certificate AS tsc', 'tsc.student_roll_number = ts.student_roll_number', 'left')
                           ->where_in('ts.student_id', $student_array)
                           ->order_by('student_roll_number', 'ASC')
                           ->get()->result();
        return $result;
    }
    
    
 /*
 * @Function            : get_person_id_by_roll
 * @Description         : get student's person id by student roll
 * @Used In             : prepare_certificate_detail
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 27-02-17
 * @Parameter           : $student_roll        
 * @Status              : Available       
 */
    public function get_person_id_by_roll($student_roll){
        $result = $this->db->select('person_id')
                           ->from('tbl_student')
                           ->where('student_roll_number', $student_roll)
                           ->get()->row();
        return $result->person_id;
    }

 /*
 * @Function            : get_current_logo
 * @Description         : get current certificate logo
 * @Used In             : prepare_certificate_detail
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 27-02-17
 * @Parameter           : $course_batch_id        
 * @Status              : Available       
 */
    public function get_current_logo($course_batch_id) {
        $result = $this->db->select('tcf.certificate_image')
                           ->from('tbl_certificate AS tcf')
                           ->join('tbl_course AS tc', 'tc.course_id = tcf.assign_to_type_id')
                           ->join('tbl_course_batch AS tcb', 'tcb.course_id = tc.course_id')                           
                           ->where('tcb.course_batch_id', $course_batch_id)
                           ->get()->row();
        return $result;
    }
    
    
/*
 * @Function            : get_course_by_name
 * @Description         : gets the course createor's name
 * @Used In             : prepare_certificate_detail
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 27-02-17
 * @Parameter           : $course_batch_id        
 * @Status              : Available       
 */
    public function get_course_by_name($course_batch_id){
        $result = $this->db->select('tp.person_first_name, tp.person_last_name')
                           ->from('tbl_course_batch AS tcb')
                           ->join('tbl_course AS tc', 'tc.course_id = tcb.course_id')
                           ->join('tbl_person AS tp', 'tp.person_id = tc.person_id')
                           ->where('tcb.course_batch_id', $course_batch_id)
                           ->get()->row();
        return $result->person_first_name.' '.$result->person_last_name;
    }
    
 /*
 * @Function            : get_certificate_course_info_by_batch_id
 * @Description         : get course detail info for certificate
 * @Used In             : prepare_certificate_detail
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 27-02-17
 * @Parameter           : $course_batch_id        
 * @Status              : Available       
 */   
    public function get_certificate_course_info_by_batch_id($course_batch_id){
        $result = $this->db->select('tc.course_name, '
                                    . 'tcb.course_batch_finish_date AS course_completion_date,'
                                    . ' tc.resource_person_id')
                           ->from('tbl_course_batch AS tcb')
                           ->join('tbl_course AS tc', 'tc.course_id = tcb.course_id')
                           ->where('tcb.course_batch_id', $course_batch_id)
                           ->get()->row();
        return $result;
                           
    }
    
/*
 * @Function            : get_resource_person_name
 * @Used In             : prepare_certificate_detail
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 27-02-17
 * @Parameter           : $resource_person_id        
 * @Status              : Available       
 */ 
    
    public function get_resource_person_name($resource_person_id){
        $result = $this->db->select('tp.person_first_name, tp.person_last_name')
                           ->from('tbl_resource_person AS trp')
                           ->join('tbl_person AS tp', 'tp.person_id = trp.person_id')
                           ->where('trp.resource_person_id', $resource_person_id)
                           ->get()->row();
        return $result->person_first_name.' '.$result->person_last_name;
    }
    
    
/*
 * @Function            : get_certificate_template
 * @Used In             : get the template identifier for certificate
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 28-02-17
 * @Parameter           : $course_batch_id        
 * @Status              : Available       
 */ 
    public function get_certificate_template($course_batch_id){
        $result = $this->db->select('tcf.certificate_template')
                           ->from('tbl_certificate AS tcf')
                           ->join('tbl_course AS tc', 'tc.course_id = tcf.assign_to_type_id')
                           ->join('tbl_course_batch AS tcb', 'tcb.course_id = tc.course_id')                           
                           ->where('tcb.course_batch_id', $course_batch_id)
                           ->get()->row();
        
        return $result->certificate_template;
    }
    
    /*
 * @Function            : save_certificate
 * @Used In             : save certificate to database
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 28-02-17
 * @Parameter           : $data       
 * @Status              : Available       
 */ 
    public function save_certificate($data){
        $this->db->insert('tbl_student_certificate', $data);
    }
    
    
 /*
 * @Function            : check_certificate_existance
 * @Used In             : Checks Certificate already exist or not
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 28-02-17
 * @Parameter           : $student_roll, $course_batch_id   
 * @Status              : Available       
 */ 
    public function check_certificate_existance($student_roll, $course_batch_id){
        $result = $this->db->select('*')->from('tbl_student_certificate')
                           ->where('course_batch_id', $course_batch_id)
                           ->where('student_roll_number', $student_roll)
                           ->get()->row();
        return $result;
    }
    
    
    
 /*
 * @Function            : get_certificate_dtl_by_batch_id
 * @Description In             : Retrives certificate data by course batch id
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 28-02-17
 * @Parameter           : $course_batch_id   
 * @Status              : Available       
 */ 
    public function get_certificate_dtl_by_batch_id($course_batch_id){
        $result = $this->db->select('*')->from('tbl_student_certificate')
                            ->where('course_batch_id', $course_batch_id)->get()->row();
        return $result;
    }
    

/*
 * @Function            : cancel_batch_student_certificate
 * @Description In             : Updates certificate status to cancel
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 28-02-17
 * @Parameter           : $student_roll, $course_batch_id  
 * @Status              : Available       
 */   
    public function cancel_batch_student_certificate($student_roll, $course_batch_id){
        $this->db->where('course_batch_id', $course_batch_id)
                 ->where('student_roll_number', $student_roll)
                 ->delete('tbl_student_certificate');
    }
    
/*
 * @Function            : certificate_info_for_view
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 01-03-17
 * @Parameter           : $student_roll, $course_batch_id  
 * @Status              : Available       
 */
    public function certificate_info_for_public_view($student_roll){
        $result = $this->db->select('*')
                           ->from('tbl_student_certificate')
                           ->where('student_roll_number', $student_roll)
                           ->get()->row();           
        return $result;
    }
    
/*
 * @Function            : get_student_name_by_id
 * @Description In      : get student name by person_id
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 01-03-17
 * @Parameter           : $person_id 
 * @Status              : Available       
 */ 
    public function get_student_name_by_id($person_id){
        $result = $this->db->select('person_first_name, person_last_name')
                            ->from('tbl_person')->where('person_id', $person_id)
                                ->get()->row();
        return $result->person_first_name.' '.$result->person_last_name;
    }
    
/**
 * @Function            : get_student_data
 * @Description In      : get student detail using search param
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 09-03-17
 * @Parameter           : $person_id 
 * @Status              : Available       
 */ 
    public function get_student_data($param){
        $result = $this->db->select('tp.person_id, tp.person_first_name, tp.person_last_name, tp.person_email, tu.url')
                           ->from('tbl_person as tp')
                           ->join('tbl_url as tu', 'tu.url_id = tp.url_id')
                           ->where('person_status', 1)
                           ->like('person_first_name', $param)
                           ->or_like('person_last_name', $param)
                           ->or_like('person_email', $param)
                           ->get()->result();
        return $result;
    }
    
/**
 * @Function            : get_certificate_detail
 * @Description In      : get certificate detail using person id
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 11-03-17
 * @Parameter           : $person_id 
 * @Status              : Available       
 */ 
    public function get_certificate_detail($person_id){
        $result = $this->db->select('tsc.course_name, tcb.course_batch_name, tsc.student_roll_number')
                            ->from('tbl_student_certificate AS tsc')
                            ->join('tbl_course_batch AS tcb', 'tcb.course_batch_id = tsc.course_batch_id')
                            ->where('tsc.person_id', $person_id)
                            ->get()->result();
        return $result;
    }
    
    
    /**
 * @Function            : get_student_person_id
 * @Description In      : get student person id using student roll number
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 12-03-17
 * @Parameter           : $student_roll 
 * @Status              : Available       
 */ 
    public function get_student_person_id($student_roll){
        $result = $this->db->select('person_id')->from('tbl_student_certificate')
                                ->where('student_roll_number', $student_roll)
                                    ->get()->row();
        return $result;
    }
    
 /**
 * @Function            : update_permanent_certificate
 * @Description In      : Updates certificate data
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 12-03-17
 * @Parameter           : $form_data, $student_roll
 * @Status              : Available       
 */ 
    public function update_permanent_certificate($form_data, $student_roll){
        $tbl_crft = array(
                    'course_by' => $form_data['course_by'],
                    'course_name' => $form_data['course_name'],
                    'course_completion_date' => $form_data['course_completion_date'],
                    'course_resource_person' => $form_data['course_resource_person'],
                );
        $this->db->where('student_roll_number', $student_roll)->update('tbl_student_certificate', $tbl_crft);
    }
    
     /**
 * @Function            : update_student_name
 * @Developed By        : Md Mashqur Ul Alam
 * @Start DateTime      : 12-03-17
 * @Parameter           : $form_data, $person_id
 * @Status              : Available       
 */ 
    public function update_student_name($form_data, $person_id){
        $tbl_prsn = array(
                    'person_first_name' => $form_data['first_name'],
                    'person_last_name' => $form_data['last_name'],
        );        
        $this->db->where('person_id', $person_id)->update('tbl_person', $tbl_prsn);
    }
        
   /*---------------- Permanent Certification start Here -------------------*/
}
