<?php

namespace App\Models;

use CodeIgniter\Model;

class AdminModel extends Model
{
    protected $db;
    protected $session;

    public function __construct()
    {
        parent::__construct();
        $this->db      = \Config\Database::connect();
        $this->session = \Config\Services::session();
    }

    public function login()
    {
        return $this->db->table('users')
            ->where('status', '1')
            ->where('user_name', $_POST['user_name'])
            ->where('password', md5($_POST['user_password']))
            ->get()
            ->getRowArray();
    }

    public function view_center()
    {
        $user_type = $this->session->get('user_type');
        $user_name = $this->session->get('user_name');
        $builder   = $this->db->table('users');
        $builder->select('users.*');
        $builder->where('user_type', '0');
        if ($user_type == 2) {
            $builder->where('counselor_email', $user_name);
        }
        $builder->orderBy('uid', 'desc');
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_center($id)
    {
        $builder = $this->db->table('users');
        $builder->where('uid', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function update_center($id)
    {
        $row = [
            'name'       => $_POST['first_name'],
            'mobile'     => $_POST['mobile'],
            'email'      => $_POST['email'],
            'work_for'   => $_POST['university'],
            'status'     => $_POST['status'],
            'updated_at' => date("Y-m-d H:i:s"),
        ];
        $builder = $this->db->table('users');
        $builder->where('uid', $id);
        $builder->update($row);
    }

    public function add_center()
    {
        $row = [
            'name'          => $_POST['first_name'],
            'user_type'     => '0',
            'mobile'        => $_POST['mobile'],
            'email'         => $_POST['email'],
            'password'      => md5(trim($_POST['password'])),
            'user_name'     => trim($_POST['user_name']),
            'parent_id'     => $this->session->get('user_id'),
            'work_for'      => $_POST['university'],
            'status'        => $_POST['status'],
            'ip_address'    => $_SERVER['REMOTE_ADDR'],
            'show_password' => $_POST['password'],
            'created_at'    => date("Y-m-d H:i:s"),
            'updated_at'    => date("Y-m-d H:i:s"),
        ];
        $builder = $this->db->table('users');
        $builder->insert($row);
        return $id = $this->db->insertID();
    }

    public function delete_user($id)
    {
        $builder = $this->db->table('users');
        $builder->where('uid', $id);
        $builder->delete();
    }

    public function change_center_status($id, $status)
    {
        if ((isset($id) && $id != "") && (isset($status) && $status != "")) {
            $row     = ['status' => $status];
            $builder = $this->db->table('users');
            $builder->where('uid', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function add_courses()
    {
        $row = [
            'name'         => $_POST['cousre_name'],
            'years'        => $_POST['course_year'],
            'status'       => $_POST['status'],
            'time_created' => time(),
            'ip_address'   => $_SERVER['REMOTE_ADDR'],
        ];
        $builder = $this->db->table('courses');
        $builder->insert($row);
        return $id = $this->db->insertID();
    }

    public function all_course_list()
    {
        $builder = $this->db->table('courses');
        $builder->select('courses.*');
        $query = $builder->get();
        return $query->getResult();
    }

    public function change_course_status($id, $status)
    {
        if ((isset($id) && $id != "") && (isset($status) && $status != "")) {
            $row     = ['status' => $status];
            $builder = $this->db->table('courses');
            $builder->where('id', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function delete_course($id)
    {
        $builder = $this->db->table('courses');
        $builder->where('id', $id);
        $builder->delete();
        return $this->db->affectedRows();
    }

    public function get_course_by_id($id)
    {
        $builder = $this->db->table('courses');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function update_course($id)
    {
        $row = [
            'name'   => $_POST['cousre_name'],
            'years'  => $_POST['course_year'],
            'status' => $_POST['status'],
        ];
        $builder = $this->db->table('courses');
        $builder->where('id', $id);
        $builder->update($row);
        return $this->db->affectedRows();
    }

    public function get_courses()
    {
        $query = $this->db->table('courses')->get();
        return $query->getResultArray();
    }

    public function get_courses_by_id($id)
    {
        $builder = $this->db->table('courses');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_sub_courses_by_id($id)
    {
        $builder = $this->db->table('sub_coures');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function add_sub_courses()
    {
        $total = $_POST['service_charge'] + $_POST['prospectus_fee'] + $_POST['exam_fee'] + ($_POST['fee'] * $_POST['crs_fee'] / 100);
        $row   = [
            'course_id'       => $_POST['course_id'],
            'name'            => $_POST['cousre_name'],
            'fee'             => $_POST['fee'],
            'exam_fee'        => $_POST['exam_fee'],
            'credit_transfer' => $_POST['credit_transfer'],
            'lateral_fees'    => $_POST['lateral_fees'],
            'total'           => $_POST['lateral_fees'],
            'status'          => $_POST['status'],
            'time_created'    => time(),
            'ip_address'      => $_SERVER['REMOTE_ADDR'],
        ];
        $builder = $this->db->table('sub_coures');
        $builder->insert($row);
        return $id = $this->db->insertID();
    }

    public function sub_course_list()
    {
        $builder = $this->db->table('sub_coures');
        $builder->select('sub_coures.*');
        $builder->orderBy('id', 'asc');
        $query = $builder->get();
        return $query->getResult();
    }

    public function change_sub_course_status($id, $status)
    {
        if ((isset($id) && $id != "") && (isset($status) && $status != "")) {
            $row     = ['status' => $status];
            $builder = $this->db->table('sub_coures');
            $builder->where('id', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function delete_sub_course($id)
    {
        $builder = $this->db->table('sub_coures');
        $builder->where('id', $id);
        $builder->delete();
        return $this->db->affectedRows();
    }

    public function get_sub_course_by_id($id)
    {
        $builder = $this->db->table('sub_coures');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function update_sub_course($id)
    {
        $total = $_POST['service_charge'] + $_POST['prospectus_fee'] + $_POST['exam_fee'] + ($_POST['fee'] * $_POST['crs_fee'] / 100);
        $row   = [
            'course_id'       => $_POST['course_id'],
            'name'            => $_POST['cousre_name'],
            'fee'             => $_POST['fee'],
            'exam_fee'        => $_POST['exam_fee'],
            'credit_transfer' => $_POST['credit_transfer'],
            'lateral_fees'    => $_POST['lateral_fees'],
            'total'           => $_POST['lateral_fees'],
            'status'          => $_POST['status'],
            'time_created'    => time(),
            'ip_address'      => $_SERVER['REMOTE_ADDR'],
        ];
        $builder = $this->db->table('sub_coures');
        $builder->where('id', $id);
        $builder->update($row);
        return $this->db->affectedRows();
    }

    public function get_student_details($year, $type)
    {
        $user_id = $this->session->get('user_id');
        if ($type == 'pending') {
            if ($user_id == '1') {
                $query = "SELECT * FROM student WHERE status='1' AND calender_from='$year' AND (enroll_num IS NULL OR enroll_num ='') ORDER BY id desc Limit 500";
            } else {
                $query = "SELECT * FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year' AND (enroll_num IS NULL OR enroll_num ='') ORDER BY id desc";
            }
        }
        if ($type == 'enrolled') {
            if ($user_id == '1') {
                $query = "SELECT * FROM student WHERE status='1' AND calender_from='$year' AND  enroll_num <>'' ORDER BY id desc";
            } else {
                $query = "SELECT * FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year' AND enroll_num <>'' ORDER BY id desc";
            }
        }
        if ($type == 'total') {
            if ($user_id == '1') {
                $query = "SELECT * FROM student WHERE status='1' AND calender_from='$year' ORDER BY id desc  limit 10";
            } else {
                $query = "SELECT * FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year' ORDER BY id desc  limit 10";
            }
        }
        if ($type == 're_reg') {
            if ($user_id == '1') {
                $query = "SELECT * FROM student WHERE status='1' AND calender_from='$year' and re_reg<>'' ORDER BY id desc  limit 50";
            } elseif ($user_id == '2') {
                $parent_id = $this->session->get('parent_id');
                $center_id = get_team_lead_center($parent_id, 'string');
                $query     = "SELECT * FROM student WHERE status='1' AND calender_from='$year' and re_reg<>'' AND user_id IN($center_id) ORDER BY id desc  limit 50";
            } else {
                $query = "SELECT * FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year'  and re_reg<>'' ORDER BY id desc  limit 50";
            }
        }
        if ($type == 'back_paper') {
            if ($user_id == '1') {
                $query = "SELECT * FROM student WHERE status='1' AND calender_from='$year' and back_paper<>'' ORDER BY id desc  limit 50";
            } else {
                $query = "SELECT * FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year'  and back_paper<>'' ORDER BY id desc  limit 50";
            }
        }
        $query = $this->db->query($query);
        return $query->getResult();
    }

    public function get_center_by_id($id = NULL)
    {
        if ($id > 0) {
            $query = "SELECT * FROM users WHERE status='1' AND user_type=0 AND uid='$id' ORDER BY name asc";
        } else {
            $query = "SELECT * FROM users WHERE status='1' AND user_type=0 ORDER BY name asc";
        }
        $query = $this->db->query($query);
        return $query->getResultArray();
    }

    public function sub_course_by_courseid($course_id)
    {
        $builder = $this->db->table('sub_coures');
        $builder->where('course_id', $course_id);
        $builder->where('status', '1');
        $builder->orderBy('id', 'desc');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_state_list()
    {
        $builder = $this->db->table('statelist');
        $builder->select('DISTINCT(state)');
        $builder->orderBy('state', 'asc');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_city_by_state($state)
    {
        $builder = $this->db->table('statelist');
        $builder->select('statelist.*');
        $builder->where('state', $state);
        $builder->orderBy('city_name', 'asc');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_city_by_id($id)
    {
        $builder = $this->db->table('statelist');
        $builder->select('statelist.*');
        $builder->where('city_id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function add_student()
    {
        $calender_from = $_POST['admission_session'];
        $time_created  = time();
        $name          = ucwords(strtolower($_POST['student_name']));
        $sub_course_id = $_POST['sub_course_id'];
        if ($sub_course_id == '') {
            $sub_course_id = 0;
        }
        $year = $_POST['year'];
        if ($year == '') {
            $year = 0;
        }
        $feeArray1      = $this->get_sub_courses_by_id($sub_course_id);
        $feeArray       = $feeArray1[0];
        $service_charge = $feeArray['service_charge'];
        $prospectus_fee = $feeArray['prospectus_fee'];
        $fee            = $feeArray['fee'];
        $crs_fee        = $this->session->get('crs_fee');
        $user_id        = $this->session->get('user_id');
        $crs_fee_in_rs  = $fee * $crs_fee / 100;
        $exam_fee       = $feeArray['exam_fee'];
        $late_fee       = 0;
        $total          = $exam_fee + $crs_fee_in_rs + $service_charge + $prospectus_fee + $late_fee;
        if ($_FILES['image']['name'] != '') {
            $ext         = pathinfo($_FILES['image']['name'], PATHINFO_EXTENSION);
            $image       = str_replace(' ', '-', $name) . '-profile-' . $time_created . '.' . $ext;
            $this->uploadImage($_FILES['image'], $image, '200');
            $image_thumb = str_replace(' ', '-', $name) . '-thumb-profile-' . $time_created . '.' . $ext;
            $this->uploadImage($_FILES['image'], $image_thumb, '200');
        }
        $row = [
            'user_id'           => $user_id,
            'admission_type'    => $_POST['admission_type'],
            'admission_session' => $_POST['admission_session'],
            'name'              => ucwords(strtolower(trim($_POST['student_name']))),
            'father_name'       => ucwords(strtolower($_POST['father_name'])),
            'mother_name'       => ucwords(strtolower($_POST['mother_name'])),
            'gender'            => $_POST['gender'],
            'dob'               => $_POST['dob'],
            'address'           => ucwords(strtolower($_POST['address'])),
            'state'             => $_POST['state'],
            'city'              => $_POST['city'],
            'pin_code'          => $_POST['pin_code'],
            'area'              => $_POST['area'],
            'phone'             => $_POST['mobile'],
            'email_id'          => $_POST['email_id'],
            'nationality'       => $_POST['nationality'],
            'cast_category'     => $_POST['cast_category'],
            'employment_status' => $_POST['employment_status'],
            'company_name'      => $_POST['emp_company_name'],
            'designation'       => $_POST['emp_designation'],
            'course_id'         => $_POST['course_id'],
            'sub_course_id'     => $sub_course_id,
            'year'              => $year,
            'service_charge'    => $service_charge,
            'prospectus_fee'    => $prospectus_fee,
            'crs_fee'           => $crs_fee,
            'crs_fee_in_rs'     => $crs_fee_in_rs,
            'exam_fee'          => $exam_fee,
            'fee'               => $fee,
            'late_fee'          => $late_fee,
            'total'             => $total,
            'adhar_card'        => $_POST['adhar_card'],
            'status'            => $_POST['status'],
            'calender_from'     => $calender_from,
            'image_thumb'       => $image_thumb,
            'image'             => $image,
            'time_created'      => $time_created,
            'created_date'      => date("Y-m-d H:i:s"),
            'updated_date'      => date("Y-m-d H:i:s"),
            'ip_address'        => $_SERVER['REMOTE_ADDR'],
        ];
        $builder = $this->db->table('student');
        $builder->insert($row);
        $inserted_id = $this->db->insertID();
        if ($inserted_id > 1) {
            for ($i = 10; $i <= 14; $i++) {
                $year                  = $_POST['year' . $i];
                $subject               = $_POST['subject' . $i];
                $board                 = $_POST['board' . $i];
                $quanlification_status = $_POST['quanlification_status' . $i];
                $type                  = $i;
                if ($year != '' && $subject != '') {
                    $row = [
                        'student_id'            => $inserted_id,
                        'type'                  => $type,
                        'year'                  => $year,
                        'subject'               => $subject,
                        'board'                 => $board,
                        'quanlification_status' => $quanlification_status,
                        'time_created'          => $time_created,
                        'ip_address'            => $_SERVER['REMOTE_ADDR'],
                        'status'                => '1',
                    ];
                    $this->db->table('student_qualification')->insert($row);
                    $result = $this->db->insertID();
                }
            }
        }
        return $inserted_id;
    }

    public function uploadImage($file, $fileName, $newwidth)
    {
        $image        = $file["name"];
        $uploadedfile = $file['tmp_name'];
        $filename     = stripslashes($image);
        $extension    = $this->getExtension($filename);
        $extension    = strtolower($extension);
        $extension    = $this->getExtension($filename);
        $extension    = strtolower($extension);
        if ($extension == "jpg" || $extension == "jpeg") {
            $uploadedfile = $file['tmp_name'];
            $src          = imagecreatefromjpeg($uploadedfile);
        } else if ($extension == "png") {
            $uploadedfile = $file['tmp_name'];
            $src          = imagecreatefrompng($uploadedfile);
        } else {
            $src = imagecreatefromgif($uploadedfile);
        }
        list($width, $height) = getimagesize($uploadedfile);
        $newheight = ($height / $width) * $newwidth;
        $tmp       = imagecreatetruecolor($newwidth, $newheight);
        imagecopyresampled($tmp, $src, 0, 0, 0, 0, $newwidth, $newheight, $width, $height);
        $filename = FCPATH . "uploads/" . $fileName;
        $result   = imagejpeg($tmp, $filename, 100);
        imagedestroy($src);
        imagedestroy($tmp);
    }

    public function uploadPdf($file, $fileName, $newwidth)
    {
        $image        = $file["name"];
        $uploadedfile = $file['tmp_name'];
        $filename     = stripslashes($image);
        $extension    = $this->getExtension($filename);
        $extension    = strtolower($extension);
        $extension    = $this->getExtension($filename);
        $extension    = strtolower($extension);
        if ($extension == "jpg" || $extension == "jpeg") {
            $uploadedfile = $file['tmp_name'];
            $src          = imagecreatefromjpeg($uploadedfile);
        } else if ($extension == "png") {
            $uploadedfile = $file['tmp_name'];
            $src          = imagecreatefrompng($uploadedfile);
        } else {
            $src = imagecreatefromgif($uploadedfile);
        }
        list($width, $height) = getimagesize($uploadedfile);
        $newheight = ($height / $width) * $newwidth;
        $tmp       = imagecreatetruecolor($newwidth, $newheight);
        imagecopyresampled($tmp, $src, 0, 0, 0, 0, $newwidth, $newheight, $width, $height);
        $filename = FCPATH . "paper-pdf/" . $fileName;
        $result   = imagejpeg($tmp, $filename, 100);
        imagedestroy($src);
        imagedestroy($tmp);
    }

    function getExtension($str)
    {
        $i = strrpos($str, ".");
        if (!$i) {
            return "";
        }
        $l   = strlen($str) - $i;
        $ext = substr($str, $i + 1, $l);
        return $ext;
    }

    public function delete_student($id)
    {
        $builder = $this->db->table('student');
        $builder->where('id', $id);
        $builder->delete();
        return $this->db->affectedRows();
    }

    public function get_student_by_id($id)
    {
        $builder = $this->db->table('student');
        $builder->where('id', $id);
        $builder->where('status', '1');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function update_student($id)
    {
        $calender_from = $_POST['admission_session'];
        $time_created  = time();
        $name          = ucwords(strtolower(trim($_POST['student_name'])));
        $sub_course_id = $_POST['sub_course_id'];
        if ($sub_course_id == '') {
            $sub_course_id = 0;
        }
        $year = $_POST['year'];
        if ($year == '') {
            $year = 0;
        }
        $feeArray1      = $this->get_sub_courses_by_id($sub_course_id);
        $feeArray       = $feeArray1[0];
        $service_charge = $feeArray['service_charge'];
        $prospectus_fee = $feeArray['prospectus_fee'];
        $fee            = $feeArray['fee'];
        $student_details = $this->get_student_by_id($id);
        $center_details  = $this->get_center($student_details['0']['user_id']);
        if ($center_details['0']['crs_fee']) {
            $crs_fee = $center_details['0']['crs_fee'];
        } else {
            $crs_fee = $this->session->get('crs_fee');
        }
        $crs_fee_in_rs = $fee * $crs_fee / 100;
        $exam_fee      = $feeArray['exam_fee'];
        if ($center_details['0']['late_fee']) {
            $late_fee = $center_details['0']['late_fee'];
        } else {
            $late_fee = 0;
        }
        $total = $exam_fee + $crs_fee_in_rs + $service_charge + $prospectus_fee + $late_fee;
        if ($_FILES['image']['name'] != '') {
            $ext         = pathinfo($_FILES['image']['name'], PATHINFO_EXTENSION);
            $image       = str_replace(' ', '-', $name) . '-profile-' . $time_created . '.' . $ext;
            $this->uploadImage($_FILES['image'], $image, '200');
            $image_thumb = str_replace(' ', '-', $name) . '-thumb-profile-' . $time_created . '.' . $ext;
            $this->uploadImage($_FILES['image'], $image_thumb, '200');
            $row     = [
                'image_thumb' => $image_thumb,
                'image'       => $image,
            ];
            $builder = $this->db->table('student');
            $builder->where('id', $id);
            $builder->update($row);
        }
        $row = [
            'admission_type'    => $_POST['admission_type'],
            'admission_session' => $_POST['admission_session'],
            'name'              => ucwords(strtolower($_POST['student_name'])),
            'father_name'       => ucwords(strtolower($_POST['father_name'])),
            'mother_name'       => ucwords(strtolower($_POST['mother_name'])),
            'gender'            => $_POST['gender'],
            'dob'               => $_POST['dob'],
            'address'           => ucwords(strtolower($_POST['address'])),
            'state'             => $_POST['state'],
            'city'              => $_POST['city'],
            'pin_code'          => $_POST['pin_code'],
            'area'              => $_POST['area'],
            'phone'             => $_POST['mobile'],
            'email_id'          => $_POST['email_id'],
            'nationality'       => $_POST['nationality'],
            'cast_category'     => $_POST['cast_category'],
            'employment_status' => $_POST['employment_status'],
            'company_name'      => $_POST['emp_company_name'],
            'designation'       => $_POST['emp_designation'],
            'course_id'         => $_POST['course_id'],
            'sub_course_id'     => $sub_course_id,
            'year'              => $year,
            'service_charge'    => $service_charge,
            'prospectus_fee'    => $prospectus_fee,
            'crs_fee'           => $crs_fee,
            'crs_fee_in_rs'     => $crs_fee_in_rs,
            'exam_fee'          => $exam_fee,
            'fee'               => $fee,
            'late_fee'          => $late_fee,
            'total'             => $total,
            'adhar_card'        => $_POST['adhar_card'],
            'status'            => $_POST['status'],
            'calender_from'     => $calender_from,
            'updated_date'      => date("Y-m-d H:i:s"),
            'ip_address'        => $_SERVER['REMOTE_ADDR'],
        ];
        $builder = $this->db->table('student');
        $builder->where('id', $id);
        $builder->update($row);
        $status = $this->db->affectedRows();
        if ($status > 0) {
            $this->db->table('student_qualification')->where('id', $id)->delete();
            for ($i = 10; $i <= 14; $i++) {
                $year                  = $_POST['year' . $i];
                $subject               = $_POST['subject' . $i];
                $board                 = $_POST['board' . $i];
                $quanlification_status = $_POST['quanlification_status' . $i];
                $type                  = $i;
                if ($year != '' && $subject != '') {
                    $row = [
                        'student_id'            => $id,
                        'type'                  => $type,
                        'year'                  => $year,
                        'subject'               => $subject,
                        'board'                 => $board,
                        'quanlification_status' => $quanlification_status,
                        'time_created'          => $time_created,
                        'ip_address'            => $_SERVER['REMOTE_ADDR'],
                        'status'                => '1',
                    ];
                    $this->db->table('student_qualification')->insert($row);
                    $result = $this->db->insertID();
                }
            }
        }
        return $inserted_id;
    }

    public function get_all_center($id, $status)
    {
        if ($status == 0) {
            if ($id > 0) {
                $query = "SELECT * FROM users WHERE uid='$id' AND user_type=0";
            } else {
                $query = "SELECT * FROM users WHERE user_type=0 ORDER BY name asc";
            }
        }
        $query = $this->db->query($query);
        return $query->getResult();
    }

    public function get_all_centers($id, $status)
    {
        if ($status == 0) {
            if ($id > 0) {
                $query = "SELECT * FROM users WHERE uid='$id' AND user_type=0";
            } else {
                $user_type = $this->session->get('user_type');
                $user_name = $this->session->get('user_name');
                if ($user_type == 2) {
                    $query = "SELECT * FROM users WHERE user_type=0 AND counselor_email ='" . $user_name . "' ORDER BY name asc";
                } else {
                    $query = "SELECT * FROM users WHERE user_type=0 ORDER BY name asc";
                }
            }
        } else {
            if ($id > 0) {
                $query = "SELECT * FROM users WHERE status='1' AND user_type=0 AND uid='$id' ORDER BY name asc";
            } else {
                $query = "SELECT * FROM users WHERE status='1' AND user_type=0 ORDER BY name asc";
            }
        }
        $query = $this->db->query($query);
        return $query->getResultArray();
    }

    public function get_center_by_user($user_name = NULL)
    {
        $builder = $this->db->table('users');
        $builder->where('user_name', $user_name);
        $builder->where('status', '1');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_user_by_user_name($user_name = NULL)
    {
        $builder = $this->db->table('users');
        $builder->where('user_name', $user_name);
        $query = $builder->get();
        return $query->getRowArray();
    }

    public function get_user_list()
    {
        $builder = $this->db->table('users');
        $builder->where('status', '1');
        $builder->where('user_type', '0');
        $builder->orderBy('uid', 'desc');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function dashboard_data()
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $data      = [];
        $data['total_lead']            = $this->total_lead(NULL);
        $data['total_30days_lead']     = $this->total_lead('30');
        $data['today_lead']            = $this->lead_status(NULL, NULL, date('Y-m-d'), NULL);
        $data['total_attempted']       = $this->lead_status('attempted', NULL, NULL, NULL);
        $data['total_not_attempted']   = $this->lead_status('notattempted', NULL, NULL, NULL);
        $data['today_attempted']       = $this->lead_status('attempted', NULL, NULL, date('Y-m-d'));
        $data['30days_attempted']      = $this->lead_status('attempted', '30', NULL, NULL);
        $data['30days_not_attempted']  = $this->lead_status('notattempted', '30', NULL, NULL);
        $data['total_opportunity']     = $this->get_activity_cont('opportunity', NULL, NULL);
        $data['30days_opportunity']    = $this->get_activity_cont('opportunity', '30', NULL);
        $data['notcontactable']        = $this->get_activity_cont('notcontactable', NULL, NULL);
        $data['30days_notcontactable'] = $this->get_activity_cont('notcontactable', '30', NULL);
        $data['notinterested']         = $this->get_activity_cont('notinterested', NULL, NULL);
        $data['30days_notinterested']  = $this->get_activity_cont('notinterested', '30', NULL);
        return $data;
    }

    public function total_lead($days = NULL)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $where     = 'status=\'1\' AND delete_status=\'0\'';
        if (isset($days) && $days != '') {
            $created_date = date('Y-m-d', strtotime("-$days days"));
            $where       .= ' AND created_at >= \'' . $created_date . '\'';
        }
        if ($user_type == '0') {
            $where .= ' AND user_id = ' . $user_id . '';
        }
        if ($user_type == '2') {
            $parent_id = $this->session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === TRUE) {
                $center_id = '0';
            }
            $where .= ' AND user_id= IN(' . $center_id . ')';
        }
        $query      = "SELECT count(id) as total_lead FROM leads WHERE  $where";
        $query      = $this->db->query($query);
        $total_lead = $query->getRowArray();
        if (sizeof($total_lead) != '0') {
            return $total_lead['total_lead'];
        } else {
            return '0';
        }
    }

    public function lead_status($type = NULL, $days = NULL, $date = NULL, $att_date = NULL)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $where     = 'status=\'1\' AND delete_status=\'0\'';
        if (isset($date) && $date != '') {
            $created_date = date('Y-m-d', strtotime($date));
            $where       .= ' AND DATE(created_at) = \'' . $created_date . '\'';
        } else {
            if (isset($days) && $days != '') {
                $created_date = date('Y-m-d', strtotime("-$days days"));
                $where       .= ' AND created_at >= \'' . $created_date . '\'';
            }
        }
        if (isset($type) && $type == 'attempted') {
            $where .= ' AND attempted >= \'1\'';
        }
        if (isset($type) && $type == 'notattempted') {
            $where .= ' AND attempted >= \'0\'';
        }
        if (isset($att_date) && $att_date != '') {
            $attempted_date = date('Y-m-d', strtotime($att_date));
            $where         .= ' AND DATE(attempted_date) = \'' . $attempted_date . '\'';
        }
        if ($user_type == '0') {
            $where .= ' AND user_id = ' . $user_id . '';
        }
        if ($user_type == '2') {
            $parent_id = $this->session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === TRUE) {
                $center_id = '0';
            }
            $where .= ' AND user_id= IN(' . $center_id . ')';
        }
        $query      = "SELECT count(id) as total_lead FROM leads WHERE  $where";
        $query      = $this->db->query($query);
        $total_lead = $query->getRowArray();
        if (sizeof($total_lead) != '0') {
            return $total_lead['total_lead'];
        } else {
            return '0';
        }
    }

    public function change_password()
    {
        $id = $this->session->get('user_id');
        if ((isset($_POST['new_password']) && $_POST['new_password'] != "") && (isset($id) && $id != "")) {
            $row = [
                'password'     => md5(trim($_POST['new_password'])),
                'show_password'=> trim($_POST['new_password']),
                'updated_date' => date("Y-m-d H:i:s"),
            ];
            $builder = $this->db->table('users');
            $builder->where('uid', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function get_activity_cont($type, $days, $date)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $where     = 'status=\'1\'';
        if (isset($date) && $date != '') {
            $created_date = date('Y-m-d', strtotime($date));
            $where       .= ' AND DATE(created_at) = \'' . $created_date . '\'';
        } else {
            if (isset($days) && $days != '') {
                $created_date = date('Y-m-d', strtotime("-$days days"));
                $where       .= ' AND created_at >= \'' . $created_date . '\'';
            }
        }
        if (isset($type) && $type == 'notcontactable') {
            $where .= ' AND activity_type = \'7\'';
        }
        if (isset($type) && $type == 'notinterested') {
            $where .= ' AND activity_type = \'9\'';
        }
        if (isset($type) && $type == 'opportunity') {
            $where .= ' AND opportunity <>  \'\'';
        }
        if ($user_type == '0') {
            $where .= ' AND user_id = ' . $user_id . '';
        }
        if ($user_type == '2') {
            $parent_id = $this->session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === TRUE) {
                $center_id = '0';
            }
            $where .= ' AND user_id= IN(' . $center_id . ')';
        }
        $query      = "SELECT count(DISTINCT lead_id) as total_lead FROM lead_activity WHERE  $where";
        $query      = $this->db->query($query);
        $total_lead = $query->getRowArray();
        if (sizeof($total_lead) != '0') {
            return $total_lead['total_lead'];
        } else {
            return '0';
        }
    }

    public function add_question_paper()
    {
        $allow_type = ['application/x-zip', 'application/zip', 'application/x-zip-compressed', 'application/force-download', 'application/octet-stream', 'application/x-rar', 'application/rar', 'application/x-rar-compressed', 'application/force-download', 'application/octet-stream'];
        if (in_array($_FILES['paper_pdf']['type'], $allow_type)) {
            $ext          = pathinfo($_FILES['paper_pdf']['name'], PATHINFO_EXTENSION);
            $time_created = time();
            $pdf_name     = str_replace(' ', '-', $_POST['paper_code']) . '-paper-' . $time_created . '.' . $ext;
            $file_name    = FCPATH . "pdf/qusetion-paper/" . $pdf_name;
            $upload       = move_uploaded_file($_FILES['paper_pdf']['tmp_name'], $file_name);
        }
        if ($upload == '1') {
            $row = [
                'paper_code'   => $_POST['paper_code'],
                'publish_date' => date("Y-m-d H:i:s"),
                'pdf_name'     => $pdf_name,
                'created_date' => date("Y-m-d H:i:s"),
            ];
            $this->db->table('question_paper')->insert($row);
            return $id = $this->db->insertID();
        } else {
            return false;
        }
    }

    public function delete_question_paper($zip_name)
    {
        $builder = $this->db->table('question_paper');
        $builder->where('pdf_name', trim($zip_name));
        $builder->delete();
        if ($this->db->affectedRows()) {
            $file_name = FCPATH . "pdf/qusetion-paper/" . $zip_name;
            unlink($file_name);
        }
    }

    public function get_team_lead_center($parent_id)
    {
        $builder = $this->db->table('users');
        $builder->select('users.uid');
        $builder->where('status', '1');
        $builder->where('parent_id', $parent_id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_user_by_parent_id($parent_id)
    {
        $builder = $this->db->table('users');
        $builder->select('*');
        $builder->where('status', '1');
        $builder->where('parent_id', $parent_id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_counselor_list()
    {
        $builder = $this->db->table('counselor');
        $builder->select('*');
        $builder->where('status', '1');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function add_payment_info($data)
    {
        $row = [
            'user_id'        => $data['user_id'],
            'order_no'       => $data['order_id'],
            'purchase_date'  => $data['purchase_date'],
            'invoice_amount' => $data['amount_paid'],
            'status'         => $data['status'],
            'payment_status' => $data['payment_status'],
            'ip_address'     => $data['ip_address'],
            'user_agent'     => $data['user_agent'],
            'last_activity'  => $data['last_activity'],
            'created_by'     => $data['created_by'],
            'created_on'     => $data['created_on'],
        ];
        $this->db->table('payment_details')->insert($row);
        return $id = $this->db->insertID();
    }

    public function update_payment_info($data)
    {
        if ($data['order_id']) {
            $row = [
                'amount'         => $data['amount'],
                'status'         => $data['status'],
                'payment_status' => $data['payment_status'],
                'payment_mode'   => $data['payment_mode'],
                'payment_detail' => $data['payment_detail'],
                'reference_no'   => $data['reference_no'],
                'created_by'     => $data['created_by'],
                'created_on'     => $data['created_on'],
            ];
            $builder = $this->db->table('payment_details');
            $builder->where('user_id', $data['user_id']);
            $builder->where('order_no', $data['order_id']);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function get_all_news()
    {
        $builder = $this->db->table('news');
        $builder->orderBy('date_news', 'desc');
        $builder->orderBy('id', 'desc');
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_latest_news()
    {
        $builder = $this->db->table('news');
        $builder->where('status', 1);
        $builder->orderBy('date_news', 'desc');
        $builder->orderBy('id', 'desc');
        $query = $builder->get(1);
        return $query->getResultArray();
    }

    public function change_news_status($id, $status)
    {
        if ((isset($id) && $id != "") && (isset($status) && $status != "")) {
            $row     = ['status' => $status];
            $builder = $this->db->table('news');
            $builder->where('id', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function insert_news()
    {
        $row = [
            'date_news'    => date("Y-m-d"),
            'news_heading' => $_POST['news_title'],
            'status'       => $_POST['status'],
            'news_detail'  => $_POST['news_description'],
        ];
        $this->db->table('news')->insert($row);
        return $id = $this->db->insertID();
    }

    public function get_news_by_id($id)
    {
        $builder = $this->db->table('news');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function update_news($id)
    {
        $row = [
            'date_news'    => date("Y-m-d"),
            'news_heading' => $_POST['news_title'],
            'status'       => $_POST['status'],
            'news_detail'  => $_POST['news_description'],
        ];
        $builder = $this->db->table('news');
        $builder->where('id', $id);
        $builder->update($row);
        return $this->db->affectedRows();
    }

    public function change_admit_card_status($id, $status)
    {
        if ((isset($id) && $id != "") && (isset($status) && $status != "")) {
            $row     = ['download_admit_card' => $status];
            $builder = $this->db->table('users');
            $builder->where('uid', $id);
            $builder->update($row);
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function insert_counselor_feedback()
    {
        $user_id = $this->session->get('user_id');
        $row     = [
            'user_id'    => $user_id,
            'comment'    => $_POST['counselor_feedback'],
            'created_at' => date("Y-m-d H:i:s"),
        ];
        $this->db->table('counselor_feedback')->insert($row);
        return $id = $this->db->insertID();
    }

    public function get_counselor_feedback()
    {
        $user_id = $this->session->get('user_id');
        $builder = $this->db->table('counselor_feedback');
        $builder->select('counselor_feedback.*');
        if ($user_id != '1') {
            $builder->where('user_id', $user_id);
        }
        $builder->orderBy('id', 'desc');
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_user_otp($mobile_number)
    {
        $created_at = date("Y-m-d H:i:s", strtotime(date("Y-m-d H:i:s") . " -15 minutes"));
        $builder    = $this->db->table('otp');
        $builder->select('otp.*');
        $builder->where('mobile_number', $mobile_number);
        $builder->orderBy('id', 'desc');
        $builder->limit(1);
        $query = $builder->get();
        return $query->getResult();
    }

    public function update_otp_status($id)
    {
        $row = [
            'status'     => '1',
            'updated_at' => date("Y-m-d H:i:s"),
        ];
        $builder = $this->db->table('otp');
        $builder->where('id', $id);
        $builder->update($row);
        if ($this->db->affectedRows()) {
            return $this->db->affectedRows();
        } else {
            return false;
        }
    }

    public function get_center_details($username)
    {
        $builder = $this->db->table('users');
        $builder->select('users.*');
        $builder->where('user_name', $username);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_counselor_by_name($name)
    {
        $builder = $this->db->table('counselor');
        $builder->select('counselor.*');
        $builder->where('counselor_name', $name);
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_counselor_by_email($email)
    {
        $builder = $this->db->table('counselor');
        $builder->select('counselor.*');
        $builder->where('counselor_email', $email);
        $query = $builder->get();
        return $query->getRowArray();
    }

    public function get_counselor_by_id($id)
    {
        $builder = $this->db->table('counselor');
        $builder->select('counselor.*');
        $builder->where('id', $id);
        $query = $builder->get();
        return $query->getRowArray();
    }

    public function update_last_login($uid)
    {
        $row     = ['last_login' => date("Y-m-d H:i:s")];
        $builder = $this->db->table('users');
        $builder->where('uid', $uid);
        $builder->update($row);
        return $this->db->affectedRows();
    }

    public function latest_login_centers()
    {
        $builder = $this->db->table('users');
        $builder->select('users.*');
        $builder->where('user_type', '0');
        $builder->orderBy('last_login', 'desc');
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_user_name_list()
    {
        $builder = $this->db->table('users');
        $builder->select('users.user_name,users.uid');
        $builder->where('user_type', '0');
        $builder->where('status', '1');
        $builder->orderBy('uid', 'desc');
        $query = $builder->get();
        return $query->getResultArray();
    }

    public function get_sub_center_by_id($user_id)
    {
        $builder = $this->db->table('users');
        $builder->select('users.uid');
        $builder->where('status', '1');
        $builder->where('user_center_id', $user_id);
        $builder->orWhere('uid', $user_id);
        $query = $builder->get();
        return $query->getResultArray();
    }
}